10.9 Overloading

You can overload the procedure or function in a package. The best example of overloading is the Oracle built-in DBMS_OUTPUT package. It provides a print procedure to display a string:

 PUT_LINE(parameter VARCHAR2) 

Other types can be printed including a DATE and NUMBER type. This leads to overloaded procedures:

 PUT_LINE(parameter VARCHAR2) PUT_LINE(parameter DATE) PUT_LINE(parameter NUMBER) 

You overload procedures when identical functionality is performed on different types. You can technically overload procedures that perform unrelated functions. This practice makes code that uses your package vague, hard to read, and hard to understand.

Subprograms within a package can be overloaded only when the parameter profile is different. Oracle must be able to determine which procedure you call. The following package contains overloaded procedures. This example is not valid because the base type of each parameter is the same.

 CREATE OR REPLACE PACKAGE sample_pkg IS     PROCEDURE process(v_value IN NUMBER);     PROCEDURE process(v_value IN INTEGER); END sample_pkg; 

You can successfully compile this package but Oracle will not know which procedure to invoke if you make the following call.

 sample_pkg.process(2); 

This statement will fail with the Oracle message:

 PLS-00307: too many declarations of 'PROCESS' match this call. 

A modification to the SAMPLE_PKG package corrects this problem. The following code defines two procedures, each with a unique profile. This is a valid example of overloading.

 CREATE OR REPLACE PACKAGE sample_pkg IS     PROCEDURE process(v_value NUMBER);     PROCEDURE process(v_value INTEGER, v_date DATE); END sample_pkg; 

Differences in MODE do not constitute differences in profile. The following is invalid.

 CREATE OR REPLACE PACKAGE sample_pkg IS     PROCEDURE process(v_value IN  NUMBER);     PROCEDURE process(v_value OUT NUMBER); END sample_pkg; 

Differences in formal parameter names do not make the procedures unique. The following will compile but the caller must use named notation. Logically, this conflicts with the concept of overloading, which is to be able to perform similar operations of different datatypes.

 CREATE OR REPLACE PACKAGE sample_pkg IS     PROCEDURE process(v_new_salary IN NUMBER);     PROCEDURE process(v_old_salary IN NUMBER); END sample_pkg; 

For this package, the first call works, but the second does not. Oracle will raise a run-time error because it cannot determine which procedure you want to call.

 sample_pkg.process(v_new_salary=>9000); sample_pkg.process(9000); 

Overloading works when the parameter profiles are different, which includes the evaluation of the base types in conjunction with default parameters declared in the specification.

The following is a case where adding a default to an overloaded procedure breaks the code. Modify SAMPLE_PKG and add a DATE parameter with a default, showing this change as:

 CREATE OR REPLACE PACKAGE sample_pkg IS     PROCEDURE process(v_value NUMBER);     PROCEDURE process(v_value INTEGER,                       v_date DATE := SYSDATE); END sample_pkg; 

The package is now a problem. A call to the procedure process using the default date will fail. The following procedure call cannot be resolved. Oracle cannot determine if the user intends to call the first procedure or the second procedure using the default.

 sample_pkg.process(2); 

This call produces the same error ”too many declarations.

 PLS-00307: too many declarations of 'PROCESS' match this call. 

Code reviews, even informal reviews, are opportunities for feedback on the reasonableness of overloading within a package. The DBMS_OUTPUT package is always an excellent example to ponder when designing overloaded procedures.