Oracle PL/SQL Best Practices

Name

PKG-09: Simplify and encourage module usage using overloading to widen calling options.

Synopsis

Overloading (also known as static polymorphism in the world of object-oriented languages) is the ability to create two or more programs with the same name. While you can do this in the declaration section of any PL/SQL block, it’s most useful and common in package specifications.

The primary reason to overload programs in your package is to transfer the “need to know” about how to use your functionality from the user to the package itself. You anticipate the different ways that developers will want to use the packaged feature and then offer matching variations of the “same” program.

Example

DBMS_OUTPUT.PUT_LINE (“put a line on the screen”) is one of the most commonly used built-in procedures in the Oracle toolbox. It’s overloaded for three types of data, as shown in the DBMS_OUTPUT specification.

PROCEDURE DBMS_OUTPUT.PUT_LINE (A VARCHAR2);
PROCEDURE DBMS_OUTPUT.PUT_LINE (A NUMBER);
PROCEDURE DBMS_OUTPUT.PUT_LINE (A DATE);

By overloading this way, Oracle allows us to pass a string, a number, or a date to this procedure, and it automatically “does the right thing.” Ironically, Oracle could have provided just a single VARCHAR2 implementation, and the PL/SQL runtime engine would have implicitly converted numbers and dates to strings for us. What Oracle didn’t offer was an overloading for DBMS_OUTPUT.PUT_LINE that supports the Boolean datatype, resulting in this kind of error:

SQL> l 1 ...