Dear Patrick,

Recently I heard someone talk about overloading in Java. What is it, is it possible in PL/SQL and if so, how would I use it?

Ramesh Cumar

Dear Ramesh,

Overloading is a technique of creating multiple programs with the same name that can be called with different sets of parameters. It is definitely possible to apply this technique in PL/SQL, in fact, Oracle does this a lot of times in their own built-in packages. If you take a look at the SYS.STANDARD package then you will find a lot of functions called TO_CHAR, but with different parameter sets. You probably never wondered how Oracle can use the same function name for completely different tasks. It’s just as easy to write
TO_CHAR(9) which will result in ‘9’ as it is to write TO_CHAR(SYSDATE) which will result in the current date in to format specified in the NLS_DATE_FORMAT parameter, for example 29-12-15 if the format is ‘DD-MM-RR’. If you would want to get this value in a different format you can just write TO_CHAR (SYSDATE, ‘Month, DDth YYYY’) to get ‘December, 29th 2015’. As you can see they are all calls to a function with the same name, but with completely different sets of parameters.

This behavior cannot be realized by making all the parameters option, like this:

FUNCTION TO_CHAR (num_in in number default null
, date_in in date default null
, datemask_in in varchar2) return varchar2;

Because if you would want to call this function without using named parameters this call
TO_CHAR (SYSDATE) would not work, since SYSDATE returns a DATE and the function expects a number as its first parameter. Maybe it might work, because of the implicit typecasts, but you get the idea.
The way this is implemented is there are multiple functions defined in a package with the same name but different sets of parameters.

One of the packages you can take a look at, because its implementation is readable, i.e. not wrapped, is the HTP package which you can use to generate HTML output for instance in an APEX application. If you take a look at for instance the PRINT procedure. In the package specification you can see there are three implementations available for this procedure:

procedure print (cbuf in varchar2 character set any_cs DEFAULT NULL);
procedure print (dbuf in date);
procedure print (nbuf in number);

The parameters of these function differ not only in name, but also in data type, which is a requirement for the use of overloading:
Data type and/or number and/or name of parameters must differ
The compiler will not complain if you don’t completely comply with this rule, but at runtime you will not be able to use either one of them.

Consider the following package with its implementation

[PATRICK]SQL>CREATE OR REPLACE PACKAGE ol IS
              PROCEDURE p (param_in IN VARCHAR2);
               PROCEDURE p (param_in IN CHAR);
             END ol;
             /

[PATRICK]SQL>CREATE OR REPLACE PACKAGE BODY ol IS
               PROCEDURE p (param_in IN VARCHAR2)
               IS
               BEGIN
                 dbms_output.put_line(param_in);
               END p;
               PROCEDURE p (param_in IN CHAR)
               IS
               BEGIN
                 dbms_output.put_line(param_in);
               END p;
             END ol;
             /

If you want to call the procedure there is no way Oracle can decide which one to use.

[PATRICK]SQL>BEGIN
               ol.p('Hello World');
             END;
             /
  ol.p('Hello World');
  *
ERROR at line 2:
ORA-06550: Line 2, column 3:
PLS-00307: too many declarations of 'P' match this call.
ORA-06550: Line 2, column 3:
PL/SQL: Statement ignored.

Even if you were using named parameters you would get the same error. What we have here is so called ‘ambiguous overloading’. You can read more about this subject at http://www.stevenfeuerstein.com/learn/building-code-analysis.
So, there is definitely a use for overloading but you have to be careful about the parameters, especially when parameters have default values. If you run into a situation of ambiguous overloading you now know why the compiler didn’t complain, but the runtime engine does.

Happy Oracle’ing,
Patrick Barel

If you have any comments on this subject or you have a question you want answered, please send an email to patrick[at]bar-solutions[dot]com. If I know the answer, or can find it for you, maybe I can help.

This question has been published in OTech Magazine of Summer 2015.