Polymorphism in Oracle

Polymorphism is a feature of object-oriented programming, is the ability to create a variable, a function, or an object that has more than one form.

In Oracle procedural programming also supports polymorphishm in the form of program unit overloading inside a package, member function type etc.

Consider the below package

CREATE OR REPLACE package addition
is
function adding(n integer,m integer) return integer;
function adding(n date,m integer) return date;
end addition;
/

CREATE OR REPLACE package body addition
is

function adding(n integer,m integer) return integer
is
begin
return n+m;
end;

function adding(n date,m integer) return date
is
begin
return n+m;
end;

end addition;
/

Above I have created an overloaded function “adding”, to add two integer and add number of days to date.

begin
dbms_output.put_line(‘adding 3 and 6 using overloaded function adding     –>’ || addition.adding(3,6));
dbms_output.put_line(’10 days from today using overloaded function adding –>’ || addition.adding(sysdate,10));
end;

and it returned as

adding 3 and 6 using overloaded function adding     –>9
10 days from today using overloaded function adding –>20-JAN-13

Function and Procedure overloading has been extensively used in many Oracle Supplied packages.
Examples:- dbms_output, dbms_sql, dbms_lob, dbms_utility etc.

Note that when you are creating overloading program units make sure to avoid unambiguous logic inside ( Other people should be able to easily understand it ). So write is neatly.

Got any questions ? Please add your comments.

2 responses to “Polymorphism in Oracle”

  1. Function and procedure overloading in oracle is based on datatype groups. For example char and varchar datatype belong to same datatype group.

    func(a char,b number);
    func(a varchar, b number);

    Above overloaded forms will create issue.