Oracle 11g Subprogram Overloading Page - 2

Page 2 of 2

Author: Saurabh Gupta Published on: 17th Jun 2011

Oracle 11g Subprogram Overloading

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.

Sample Code

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.

Sample Code

SQL> 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]

Sample Code

SQL> 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.

Sample Code

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.

Sample Code

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 parents attributes and methods. But it declares an overriding method DISPLAY to print the alternate name for it id.

Sample Code

SQL> CREATE OR REPLACE TYPE OT_OVERLOAD_TEST AS OBJECT
(ID NUMBER,
NAME VARCHAR2(100),
MEMBER FUNCTION DISPLAY RETURN VARCHAR2
)
NOT FINAL;
/
Type created.
SQL> 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> CREATE OR REPLACE TYPE OT_SUB_OVERLOAD UNDER OT_OVERLOAD_TEST
(ALTER_NAME VARCHAR2(100),
OVERRIDING MEMBER FUNCTION DISPLAY RETURN VARCHAR2);
/
Type created.
SQL> 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> 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.