Combining Overloading and Session Management in Oracle
April 12, 2006
One feature often times overlooked by developers is PL/SQL's ability to allow overloading. As in other programming languages, overloading occurs when a program uses the same name for a function or procedure, and the way the program differentiates which version being called is based on the types and numbers of parameters being passed in. By taking advantage of some basic features of PL/SQL, a DBA can quickly and easily build a versatile session management tool, which can manage one or many sessions.
A typical example of overloading
One of the simplest examples often seen in C, C++ and Java programming examples involves computing the area of rectangle. The area of a rectangle is simply the width multiplied by the length, so a function that returns the area would need two parameters passed in. In the special case of when the width and length are the same, all that is needed to compute the area is either parameter. In this case, our prototype function needs only one parameter. The two functions, or the overloaded function with differing signatures, could easily be constructed as follows:
create function compute_area (width number, length number) return number is area number; begin area:= width * length; return area; end; / create function compute_area (side number) return number is area number; begin area:=side*side; return area; end; /
Would it be correct to say that the usage is as simple as "select compute_area(5, 3) from dual" or "select compute_area(5) from dual?" The answer is yes and no. Let's start with the no part first.
Does Oracle allow objects to share the same name? The example as shown above, if run as depicted, results in an error:
create function compute_area (side number) return number is * ERROR at line 1: ORA-00955: name is already used by an existing object
What if the create statement used "create or replace" instead of "create" by itself? The last statement would then be the only one in effect. You could then compute the area of a square, but would have lost the more general "compute the area of a rectangle" functionality.
As standalone, differently named functions, the "yes" side of the answer is as shown.
To use the overloading feature, these functions need to be part of a package. Coming back to how overloading can be useful to a DBA, the functions will now become procedures which output kill session statements for input based on a SID or a username. The procedure name will be the same for both cases, but the input a number or a username will serve as the signature (i.e., what the procedure expects as input). With the addition of one more parameter (a flag to signal Oracle whether to perform the kills or just output the statements), you can easily add more flexibility to these procedures.
Being able to kill a single session based on the user's SID is obviously useful for managing individual users. Being able to kill multiple users - all with the same username, which is very common when working with Web applications is a handy feature or functionality to have around when you need to shut down, so to speak, part of an OLTP database without having to shut down the entire database.
Code the package and package body
Okay, this article is also a gentle introduction to packages. Without packages, no overloading is allowed. The first step is to create the package specification (what the package contains: names of functions and procedures with their signatures). The second step is to create the package body, which is nothing more than the code behind the procedures and functions listed in the package specification. An example of how one could code the package and package body is shown below.
create or replace package manage_users as procedure kill_user(the_sid in number); procedure kill_user(the_user in varchar2); end manage_users; / create or replace package body manage_users as procedure kill_user(the_sid in number) as v_str varchar2(50) := 'alter system kill session '||chr(39); v_serial number; begin dbms_output.put_line(chr(10)); dbms_output.put_line('Session to be killed:'); dbms_output.put_line('---------------------'); select serial# into v_serial from v$session where sid = the_sid; v_str := v_str||the_sid||','||v_serial||chr(39)||';'; dbms_output.put_line(v_str); exception when no_data_found then dbms_output.put_line('No such session.'); end; procedure kill_user(the_user in varchar2) as v_str varchar2(50) := 'alter system kill session '||chr(39); v_statement varchar2(50); cursor user_cur is select sid, serial# from v$session where username = upper(the_user); begin dbms_output.put_line(chr(10)); dbms_output.put_line('Sessions to be killed:'); dbms_output.put_line('----------------------'); for user_rec in user_cur loop v_statement := v_str||user_rec.sid||','||user_rec.serial#||chr(39)||';'; dbms_output.put_line(v_statement); v_statement := null; end loop; end; end manage_users; /
Examples of invoking or executing the package_name.package_element (manage_users.kill_user) are shown below. As what was shown in the code source, one version of the kill_user procedure accepts a name (varchar2), and the other a number.
SQL> exec manage_users.kill_user('scott'); Sessions to be killed: ---------------------- alter system kill session '141,8'; alter system kill session '142,213'; alter system kill session '145,37'; PL/SQL procedure successfully completed. SQL> exec manage_users.kill_user(141); Session to be killed: --------------------- alter system kill session '141,8'; PL/SQL procedure successfully completed.
The statements are cut and paste ready inside of a SQL*Plus session.
So far, the unstated user invoking these procedures is System. Just like non-packaged functions and procedures, users needing to execute package elements need the appropriate roles and privileges. If DML or DDL is performed within the package, the calling user needs the appropriate permission/grant/role.
Overloading allows the DBA or developer to re-purpose code and make use of existing names for functions and procedures. If an application changes (key input is now based on a different datatype, or a different number of parameters), but both the old and new versions need to be supported, it may be possible to re-purpose existing code by using overloading.