Overloading Packages, Functions & Procedures

What is Overloading and how Overloading is used by ORACLE?

Overloading is the concept of using a single name for a method with different parameter lists.

Oracle implements methods as functions or procedures. The parameter list is the set of the input variables for functions and procedures. Oracle tells the difference between overloaded functions and procedures by checking the sequential order of parameters and their data types. You call the sequential order and data types of the parameters the signature of a function.

The Same procedure name is repeated with parameters of different datatypes and parameters in different positions,  varying number of parameters is called overloading of procedures.

Example- Function available in ORACLE TO_CHAR is also an example of Overloading as it works for both ‘Converting Number to Char’ and ‘Converting Date to Char’. This is defined in ORACLE standard Package and when we call this function it identifies the type of input and call the required function implemented for the Package.

When to Overload Package Methods?

• When same action needs to be performed for different kinds or combinations of data.
• Allow developers to use a program in the most natural and intuitive fashion; you use overloading to fit your program to the needs of the user.
• Make it easy for developers to specify, unambiguously and simply, the kind of action desired.

Let’s see how Overloading works (Will define a Package) in Oracle and how we define and call them from Package.

Package Specification-

Create or Replace Package OVERLOADING_PACKAGE

AS

PROCEDURE DISPLAY_Result( p_lower IN NUMBER, p_upper IN NUMBER);

PROCEDURE DISPLAY_Result( V1 IN NUMBER, V2 IN NUMBER, V3 IN NUMBER);

PROCEDURE DISPLAY_Result( V1 Varchar2, V2 Varchar2);

End;

Package Body-

Create or Replace Package BODY  OVERLOADING_PACKAGE

As

Procedure DISPLAY_Result( p_lower IN NUMBER, p_upper IN NUMBER)

AS

Begin

DBMS_OUTPUT.PUT_LINE('First Procedure');

For i IN p_lower .. p_upper

LOOP

DBMS_OUTPUT.PUT_LINE(i);

End LOOP;

End; 

Procedure DISPLAY_Result( V1 IN NUMBER, V2 IN NUMBER, V3 IN NUMBER)

AS

Begin

DBMS_OUTPUT.PUT_LINE('Second Procedure');

DBMS_OUTPUT.PUT_LINE(V1+V2+V3);

End;

Procedure DISPLAY_Result( V1 Varchar2, V2 Varchar2)

AS

Begin

DBMS_OUTPUT.PUT_LINE('Third Procedure');

DBMS_OUTPUT.PUT_LINE(V1 || V2);

End;

End;

Let’s execute the above package with the PL/SQL Block below-

SQL> SET SERVEROUTPUT ON
SQL> BEGIN
  2      OVERLOADING_PACKAGE.DISPLAY_Result(3,6);
  3      OVERLOADING_PACKAGE.DISPLAY_Result(5,6,8);
  4      OVERLOADING_PACKAGE.DISPLAY_Result('Good',' Morning');
  5  End;
  6  /
First Procedure
3
4
5
6
Second Procedure
19
Third Procedure
Good Morning

PL/SQL procedure successfully completed.

Let’s see one more example of Package Overloading.

Package Specification-

CREATE OR REPLACE PACKAGE Overloading IS

FUNCTION Message( pv_id    NUMBER, pv_text  VARCHAR2 ) RETURN VARCHAR2;

FUNCTION Message( pv_text  VARCHAR2, pv_id    NUMBER ) RETURN VARCHAR2;

END Overloading;

Package Body-

CREATE OR REPLACE PACKAGE BODY Overloading IS

FUNCTION Message( pv_id    NUMBER, pv_text  VARCHAR2 ) RETURN VARCHAR2 IS

/* Declare a local variable. */

lv_module_name  VARCHAR2(10) := 'Module 1';

BEGIN

RETURN '['||lv_module_name||']'||'['||pv_id||']['||pv_text||']';

END Message;

FUNCTION Message( pv_text  VARCHAR2, pv_id    NUMBER ) RETURN VARCHAR2 IS

/* Declare a local variable. */

lv_module_name  VARCHAR2(10) := 'Module 2';

BEGIN

RETURN '['||lv_module_name||']'||'['||pv_id||']['||pv_text||']';

END Message;

END Overloading;

Let’s use above Package functions in DML Statements.

SQL> SELECT OVERLOADING.MESSAGE(1,'Captain America') Avenger1,
  2  OVERLOADING.MESSAGE('Thor',2) Avenger2
  3  From Dual;

AVENGER1
--------------------------------------------------------------------------------
AVENGER2
--------------------------------------------------------------------------------
[Module 1][1][Captain America]
[Module 2][2][Thor]

RELATED TOPICS
How to create PACKAGES in ORACLE and call it's Methods?
What is the difference between Stored Procedure and Functions?
How to pass parameters in ORACLE Procedures/Functions?

Get involved and leave your Comments in the Box Below. The more people get involved, the more we all benefit. So, leave your thoughts before you leave the page.