Oracle 11g Subprogram Overloading

Polymorphism is an Object Oriented Programming language feature which allows the objects to accept same input and behave differently. It can be implemented in three ways i.e. overriding, hiding, and overloading.

In Oracle, overloading is one of the polymorphism techniques which allow the creation of more than one program units, within the vicinity of block, with the same name but different parameter lists.

In this tutorial, we shall move ahead to understand the overloading techniques and restrictions in Oracle.

Overloading: Background

Overloading is not new to Oracle. It is one of the most extensively used concepts in the making of Oracle built in functions. Many of the built in functions, which are defined in the STANDARD package, DBMS_OUTPUT or others packages, are overloaded.

Code [1]:

PUT_LINE function can be used with numeric, string or date inputs as shown below.

SQL&gt; EXEC DBMS_OUTPUT.PUT_LINE(100);
100
 
PL/SQL PROCEDURE successfully completed.
 
SQL&gt; EXEC DBMS_OUTPUT.PUT_LINE('Oracle');
Oracle
 
PL/SQL PROCEDURE successfully completed.
 
SQL&gt; EXEC DBMS_OUTPUT.PUT_LINE(SYSDATE);
18-MAY-11
 
PL/SQL PROCEDURE successfully completed.

Another overloaded function TO_CHAR can be used with date, number and string inputs. Its overloaded headers are as below.

FUNCTION TO_CHAR (RIGHT DATE) RETURN VARCHAR2;
FUNCTION TO_CHAR (LEFT NUMBER) RETURN VARCHAR2;
FUNCTION TO_CHAR (LEFT DATE, RIGHT VARCHAR2) RETURN VARCHAR2;
FUNCTION TO_CHAR (LEFT NUMBER, RIGHT VARCHAR2) RETURN VARCHAR2;

Many mathematical functions accept all type of input which belongs to the same data type family. This implies that Oracle kernel is quite familiar with overloading and its benefits.

Overloading of Local and Packaged subprograms

Oracle allows overloading of subprograms in local scope, packaged scope or object type scope. The subprograms declared in these scopes can exist with same name with different signatures. Note that a ‘subprogram signature’ is a component of its name and parameter list only. As the subprogram name is same, the parameters passed to the subprograms should different in count, order, or belonging data type family.

Only local or packaged procedure and functions can be overloaded. Standalone (or stored) subprograms cannot be overloaded as they need to obey the rule of unique name and namespace categorization. Object type methods contained by super type and subtype can be overloaded and overridden too.

I shall demonstrate overloading with the help of a sample program in Code [2]

Code [2]

CREATE OR REPLACE PACKAGE PKG_OVERLOAD_TEST 
IS
   PROCEDURE P_TEST1(A NUMBER, B NUMBER);
   PROCEDURE P_TEST1(A VARCHAR2);
END;
 
CREATE OR REPLACE PACKAGE BODY PKG_OVERLOAD_TEST
IS
   PROCEDURE P_TEST1(A NUMBER, B NUMBER)
   IS
   BEGIN
      DBMS_OUTPUT.PUT_LINE('Sum of two numbers is:'||to_char(A+B));
   END;
 
   PROCEDURE P_test1( a varchar2) 
   IS
   BEGIN
      DBMS_OUTPUT.PUT_LINE('The input string is:'||A);
   END;
END;

The overloaded subprograms show different behavior based on the inputs provided.

Code [3]:

Testing the procedure call with two number inputs.

SQL&gt; BEGIN
  2     PKG_OVERLOAD_TEST.P_TEST1(1,2);
  3  END;
  4  /
SUM OF two numbers IS:3
 
PL/SQL PROCEDURE successfully completed.

When the PL/SQL engine receives the subprogram invocation call, it compares the inputs with the parameter list available for overloaded units. It executes only the one whose signature matches with the input and produces the output.

In the Code [3], the two inputs were of number type. Optimizer matches them with the overloaded modules available for PKG_OVERLOAD_TEST. Parameter list for the first subprogram header matches the call and is executed.

Similarly, second overloaded subprogram can also be tested as below with a string input.

Code [4]

SQL&gt; BEGIN
  2     PKG_OVERLOAD_TEST.P_TEST1('Exforsys');
  3  END;
  4  /
The INPUT string IS:Exforsys
 
PL/SQL PROCEDURE successfully completed.

Overloading Usage guidelines/restrictions

If the parameter list of the overloaded program units appears same in terms of count and their data types, Oracle raises exception and restricts the creation of the package. Note that Oracle allows overloading, not duplication.

Special attention should be kept towards data type of the parameters. Even if the parameter data types are different, but belong to the same data type family, Oracle prevents the overloading of subprograms.

Code [5]:

Two procedures L_CALC accept NUMBER and INTEGER type inputs. But the package body fails to compile the program unit because INTEGER and NUMBER belong to the same data type family.

DECLARE
   PROCEDURE L_CALC(A NUMBER)
   IS
   BEGIN
      DBMS_OUTPUT.PUT_LINE(A*10);
   END;
 
   PROCEDURE L_CALC(A INTEGER)
   IS
   BEGIN
      DBMS_OUTPUT.PUT_LINE(A*10);
   END;
BEGIN
   L_CALC(7);
END;
/
   L_CALC(7);
   *
ERROR at line 13:
ORA-06550: line 13, COLUMN 4:
PLS-00307: too many declarations OF 'L_CALC' MATCH this CALL
ORA-06550: line 13, COLUMN 4:
PL/SQL: Statement ignored

Oracle allows the creation of a overloaded function with same signature but different RETURN type. But at the time of execution, optimizer finds conflict of headers and raises exception PLS-00307.

Overloading is not possible if the parameters differ only in their passage mode.

Code [6]:

Locally overloaded procedures L_ADD fail to overload due to same signatures.

SQL&gt; DECLARE
  2     PROCEDURE L_ADD(A IN NUMBER, B IN NUMBER) ----//Local procedure 1
  3     IS 
  4     BEGIN
  5        DBMS_OUTPUT.PUT_LINE(A+B);
  6     END;
  7     PROCEDURE L_ADD(A IN NUMBER, B OUT NUMBER)  ----//Local procedure 2
  8     IS
  9     BEGIN
 10        B:= A;
 11     END;
 12  BEGIN
 13     L_ADD(1,2);
 14  END;
 15  /
   L_ADD(1,2);
   *
ERROR at line 13:
ORA-06550: line 13, COLUMN 4:
PLS-00307: too many declarations OF 'L_ADD' MATCH this CALL
ORA-06550: line 13, COLUMN 4:
PL/SQL: Statement ignored

Metadata for overloaded subprograms in Packages

Package metadata is captured in USER_PROCEDURES dictionary view. For overloaded subprograms, the system generates a sequence id, in order of their prototyping within the package.

Run the below query to view the package metadata along with the overloaded subprogram sequence.

Code [7]

SQL&gt; SELECT OBJECT_NAME, OBJECT_TYPE, PROCEDURE_NAME, SUBPROGRAM_ID, OVERLOAD 
     FROM USER_PROCEDURES 
     WHERE OBJECT_NAME='PKG_OVERLOAD_TEST';

Overloading in Object types

In Object types, polymorphism is practiced through Overloaded and Overridden methods. Overloading is practiced in inherited object types where the subtypes have a member method with the same name as that of inherited one, but differ in nature of parameters. Based on the member methods signatures, optimizer routes the invocation calls and produces the output.

For example, a super (parent) type CALC_OT contains multiple calculation member methods, one of which is to add 2 numbers as shown in the below definition.

CREATE OR REPLACE TYPE CALC_OT AS OBJECT
( …
 MEMBER PROCEDURE ADD(P1 NUMBER, P2 NUMBER)
);

Now a sub (child) type ADD_OT adds inherited number inputs with its owned ones. Its member subprogram can be overloaded and created with the same name as the inherited one.

CREATE OR REPLACE TYPE ADD_OT UNDER CALC_OT
(…
…
MEMBER PROCEDURE ADD(P3 NUMBER)
);

Overriding is another technique to create a subprogram in object type hierarchy with the same name. But unlike overloading, here the member method is redefined in the sub (child) type body. This eclipses the subprogram definition available in the type body of super (parent) type. If the subtype extends to some other types too, they inherit the redefined version of the overridden subprogram and not the original version.

When a type variable invokes the member method, Oracle executes the associated member subprogram with that type only.

Code [8]:

A super type OT_OVERLOAD_TEST has been created, which contains DISPLAY method to print the type attribute values. The type has been extended to OT_SUB_OVERLOAD, which inherits its parent’s attributes and methods. But it declares an overriding method DISPLAY to print the alternate name for it id.

SQL&gt; CREATE OR REPLACE TYPE OT_OVERLOAD_TEST AS OBJECT
	(ID NUMBER,
	 NAME VARCHAR2(100),
	 MEMBER FUNCTION DISPLAY RETURN VARCHAR2
	 )
      NOT FINAL;
	/	
 
TYPE created.
 
SQL&gt; CREATE OR REPLACE TYPE BODY OT_OVERLOAD_TEST AS
        MEMBER FUNCTION DISPLAY RETURN VARCHAR2
        IS
        BEGIN
           RETURN 'Name for the id:'||to_char(ID)||' is '||NAME;
        END;
    END;
    /
TYPE body created.
 
SQL&gt; CREATE OR REPLACE TYPE OT_SUB_OVERLOAD UNDER OT_OVERLOAD_TEST
     (ALTER_NAME VARCHAR2(100),
      OVERRIDING MEMBER FUNCTION DISPLAY RETURN VARCHAR2);
      /
 
TYPE created.
 
SQL&gt; CREATE OR REPLACE TYPE BODY OT_SUB_OVERLOAD AS
      OVERRIDING MEMBER FUNCTION DISPLAY RETURN VARCHAR2
      IS
      BEGIN
         RETURN 'Alternate name for the id:'||to_char(SELF.ID)||' is '||ALTER_NAME;
      END;
    END;
    /
 
TYPE body created.
 
SQL&gt; DECLARE
  2  l_parent ot_overload_test := ot_overload_test(100,'Exforsys');
  3  l_child ot_sub_overload := ot_sub_overload(100,'Exforsys','Oracle 11g tutorials');
  4  BEGIN
  5  dbms_output.put_line(l_parent.display());
  6  Dbms_Output.Put_Line(L_Child.Display());
  7  END;
  8  /
Name FOR the id:100 IS Exforsys
Alternate name FOR the id:100 IS Oracle 11g tutorials
 
PL/SQL PROCEDURE successfully completed.