Oracle 11g Subprogram Overloading

Page 1 of 2

Author: Saurabh Gupta Published on: 17th Jun 2011

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.

Sample Code

SQL> exec DBMS_OUTPUT.PUT_LINE(100);
100
PL/SQL procedure successfully completed.
SQL> EXEC DBMS_OUTPUT.PUT_LINE('Oracle');
Oracle
PL/SQL procedure successfully completed.
SQL> 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.

Sample Code

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]

Sample Code

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.

Sample Code

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

Ads

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.