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.

Advertisements

One thought on “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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s