Compassionate programming? What the heck is that? That’s how you write code when you care about others, in particular the other developers who will sooner or later use your code.
Overloading is another feature of PL/SQL (and, of course, many other programming languages) that I really like a lot. The basic idea is that in the declaration section of a PL/SQL block (this includes packages, anonymous blocks, procedures, functions, triggers, and object types) you can define more than one subprogram (procedure and/or function) with the same name.
Of course, something has to be different, and as the documentation says:
You can use the same name for several different subprograms if their formal parameters differ in name, number, order, or data type family. (A data type family is a data type and its subtypes.)
Also, may I add that two subprograms can have the same name and exactly the same parameter list – if one of them is a procedure and the other a function!
Here’s a very simple example of overloading in a package specification:
package pkg authid definer is procedure proc (p_n in number); procedure proc (p_vc in varchar2); end;
And here’s the same overloading in a procedure and an anonymous block:
declare
procedure proc (p_n in number) is begin null; end;
procedure proc (p_vc in varchar2) is begin null; end;
begin
proc (1);
proc ('abc');
end;
procedure with_two_nested_procs
is
procedure proc (p_n in number) is begin null; end;
procedure proc (p_vc in varchar2) is begin null; end;
begin
proc (1);
proc ('abc');
end;
So that’s the basic idea. Why do I like it so much?
Because well-designed overloading means the writer of that code was thinking about how to make that code easier and more intuitive to use. Now, that’s compassionate!
Overloading can greatly simplify your life and the lives of other developers. This technique consolidates the call interfaces for many similar programs into a single module name, transferring the burden of knowledge from the developer to the software. When you build overloaded subprograms, you spend more time in design and implementation than you might with separate, standalone programs. This additional time up-front will be repaid handsomely down the line because you and others will find it much easier and more efficient to use your programs.
Let’s explore the benefits in a bit more detail.
Supporting many data combinations
When applying the same action to different kinds or combinations of data, overloading does not provide a single name for different activities, so much as it provides different ways of requesting the same activity. This is the most common motivation for overloading. Let’s look at a somewhat surprising example when it comes to overloading: dbms_output.put_line.
As I expect you know, you can pass a number, date or string to this built-in procedure, as in:
begin
dbms_output.put_line (sysdate);
dbms_output.put_line (100);
dbms_output.put_line ('abc');
end;
It would, therefore, be very understandable if you concluded that this procedure is overloaded along these lines:
package dbms_output is procedure put_line (item in date); procedure put_line (item in varchar2); procedure put_line (item in number); end;
But you would be wrong! In fact, and oddly enough, put_line was overloaded in the distant past, but the overloading were removed in Oracle Database 10g. There is now just a single definition of put_line:
procedure put_line (item in varchar2);
When it comes to put_line, the, ahem, magical ability to display a string, number or date is the result of PL/SQL’s support for implicit conversions, not overloading. That’s why this doesn’t work:
begin dbms_output.put_line (true); end;
PL/SQL does not (yet, as of Oracle Database 21c) support implicit conversions of Booleans to strings. Fortunately, this is one of those limitations that we can fix ourselves – with overloading!
I can create a layer of code over dbms_output.put_line to make my life, and the lives of other developers on my team, easier.
package p is procedure l (dt in date, mask_in in varchar2 := null); procedure l (num in number, mask_in in varchar2 := null); procedure l (stg in varchar2); procedure l (bool in boolean); end p;
Notice the “added value” I offer in this simple extension to dbms_output:
- Reduce the amount of typing needed to say “show me something”
- Allow users to provide a format mask, instead of having to write the to_char conversion step themselves
- Support the display of Booleans
And, oh, you could offer so much more! 🙂
create or replace package p is procedure l (dt in date, mask_in in varchar2 := null); procedure l (num in number, mask_in in varchar2 := null); procedure l (stg in varchar2); procedure l (stg in varchar2, num in number); procedure l (stg in varchar2, dt in date, mask_in in varchar2 := null); procedure l (bool in boolean); procedure l (stg in varchar2, bool in boolean); procedure l (file_in in utl_file.file_type); procedure l ( procedure l (num1 in number, num2 in number); procedure l (str in varchar2, num1 in number, num2 in number); procedure l (bool1 in boolean, bool2 in boolean); procedure l (stg1 in varchar2, stg2 in varchar2); procedure l (dt1 in date, dt2 in date, mask_in in varchar2 := null); procedure l (num in number, dt in date, mask_in in varchar2 := null); procedure l (bool in boolean, num in number); procedure l (bool in boolean, dt in date, mask_in in varchar2 := null); procedure l (bool in boolean, stg in varchar2); procedure l (xml_in in sys.xmltype); procedure l (clob_in in clob); end p;
No need for a separate procedure to display clobs, print out files, etc. Just one package will do it all. Of course, you get decide how far you want to go with such things. I could probably come up with another 100 combinations for p.l, but who would use them?
Fitting the program to the user
To make your code as useful as possible, you may construct different versions of the same program that correspond to different patterns of use. This often involves overloading functions and procedures. A good indicator of the need for this form of overloading is when you find yourself writing unnecessary code. For example, when working with DBMS_SQL, you will call the DBMS_SQL.EXECUTE function, but for DDL statements, the value returned by this function is irrelevant. Oracle should have overloaded this function as a procedure, so that I could simply execute a DDL statement like this:
BEGIN
DBMS_SQL.EXECUTE ('CREATE TABLE xyz ...');
as opposed to:
DECLARE
feedback PLS_INTEGER;
BEGIN
feedback := DBMS_SQL.EXECUTE ('CREATE TABLE xyz ...');
and then ignoring the feedback.
Another example reflects a common pattern: suppose I need to get the ID for a user from their email address. But if their email address is not already in the table, then we want to add it, create the user, and return the ID. The overloaded API might look like this:
create or replace package p is procedure create_user (p_email in varchar2); function create_user (p_email in varchar2) return number; end;
Overloading by type, not value
This is the least common application of overloading. In this scenario, you use the type of data, not its value, to determine which of the overloaded programs should be executed. This really comes in handy only when you are writing very generic software. DBMS_SQL.DEFINE_COLUMN is a good example of this approach to overloading. I need to tell DBMS_SQL the type of each of my columns being selected from the dynamic query. To indicate a numeric column, I can make a call as follows:
DBMS_SQL.DEFINE_COLUMN (cur, 1, 1);
or I could do this:
DBMS_SQL.DEFINE_COLUMN (cur, 1, DBMS_UTILITY.GET_TIME);
It doesn’t matter which I do; I just need to say “this is a number,” but not any particular number. Overloading is an elegant way to handle this requirement.
But It Compiles!
So what? So your package with a bunch of overloading compiles. You think that means you should then be able to actually use/invoke your overloaded subprograms? Ha!
I hate to break the news, but it is certainly possible to overload subprograms so that your package compiles, but you cannot actually invoke those subprograms, or run into issues when trying to invoke them, namely:
PLS-00307: too many declarations of 'OVERLOADED_NAME' match this call
This can happen when….
- The only difference between the “matching” parameter datatypes is varchar2 vs char, or another pair of too-similar, but different types.
- You overload based on different subtypes that have the same base type
You can also run into situations where the package compiles, but you run into compile errors trying to use the subprograms, because….
- The only way to successfully invoke the subprogram requires the use of named notation
- You are forcing the compiler to perform implicit conversions
This LiveSQL script demonstrates some of these.
Things to Remember
- Two subprograms cannot differ only by return type.
- Differences in parameter mode (in, out, in out) are not sufficient for successful overloading
- Two functions whose only difference is the type of return value cannot be overloaded
- Default values on parameters can lead to problems with overloaded subprograms.
- Even when your package compiles, it doesn’t mean you can actually use your overloaded subprograms. Test them all!
