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.

In Closing

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.

Back to DBAsupport.com