Skip to main content

When Lazy is Good: Overloading and APIs

When more than one subprogram (procedure or function) in the same scope share the same name, the subprograms are said to be overloaded. PL/SQL supports the overloading of procedures and functions in the declaration section of a block (named or anonymous), package specifications and bodies, and object type definitions. Overloading is a very powerful feature, and you should exploit it fully to improve the usability of your software.

Before exploring some of the details of overloading, a short quiz:

Which of the following is another name for overloading?
  • Dynamic Polymorphism
  • Interface Inheritance
  • Static Polymorphism
  • Multiple Monomorphism
In a poll I conducted on Twitter, we saw the following results:
And I was very glad to see this, because Static Polymorphism is, indeed, another name for overloading, and here's why:

With overloading, at the time your code is compiled, PL/SQL resolves all references to named elements, such as a function invocation. If there is more than one subprogram with same name name, it must sort out which of those "fits". That process is described in detail below, but two salient facts are clear:

1. The resolution takes place at compile time ("static").
2. There are more than one subprogram with the same name but different "shape" (morphology, the study of the shape of things).

Dynamic polymorphism is a process specific to object-oriented languages, in which multiple classes in a hierarchy contain methods with the same name. The language processor decides at runtime which of these methods should be invoked. Runtime = dynamic. Dynamic polymorphism is supported for methods in Oracle object types, but not with PL/SQL procedures and functions. Check out my LiveSQL script for a demonstration.

Interface Inheritance is a real thing but it is not overloading. Multiple Monomorphism is some nonsense I made up. :-)

OK, that's the "theory". Let's take a look at some "practice" (actual overloading examples and features).

Here is a very simple example of three overloaded subprograms defined in the declaration section of an anonymous block (therefore, all are nested modules and can only be used within this block):
   /* First version takes a DATE parameter. */
      RETURN date_in <= SYSDATE;

   /* Second version takes a NUMBER parameter. */
      RETURN number_in > 0;

   /* Third version is a procedure! */
   PROCEDURE value_ok (number_in IN NUMBER) IS
      IF number_in > 0 THEN
         DBMS_OUTPUT.PUT_LINE (number_in || 'is OK!');
         DBMS_OUTPUT.PUT_LINE (number_in || 'is not OK!');
      END IF;

When the PL/SQL runtime engine encounters the following statement:
IF value_ok (SYSDATE) THEN ...
the actual parameter list is compared with the formal parameter lists of the various overloaded modules, searching for a match. If one is found, PL/SQL executes the code in the body of the program with the matching header.

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. You do not have to try to remember, for instance, the six different names for programs adding values (dates, strings, Booleans, numbers, etc.) to various collections. Instead, you simply tell the compiler that you want to add a value and pass it that value. PL/SQL and your overloaded programs figure out what you want to do and then do it for you.

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.

Benefits of Overloading

There are three different scenarios that benefit from overloading:

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.

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:
as opposed to:
   feedback PLS_INTEGER;
   feedback := DBMS_SQL.EXECUTE ('CREATE TABLE xyz ...');
and then ignoring the feedback.

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:
or I could do this:
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.

Let’s look at an example of the most common type of overloading and then review restrictions and guidelines on overloading.

Supporting many data combinations

Use overloading to apply the same action to different kinds or combinations of data. As noted previously, this kind of overloading does not provide a single name for different activities so much as different ways of requesting the same activity. Consider DBMS_OUTPUT.PUT_LINE. You can use this built-in to display the value of any type of data that can be implicitly or explicitly converted to a string.

Interestingly, in earlier versions of Oracle Database (7, 8, 8i, 9i), this procedure was overloaded. In Oracle Database 10g and later, however, it is not overloaded at all! This means that if you want to display an expression that cannot be implicitly converted to a string, you cannot call DBMS_OUTPUT.PUT_LINE and pass it that expression.

You might be thinking: so what? PL/SQL implicitly converts numbers and dates to a string. What else might I want to display? Well, for starters, how about a Boolean? To display an expression of type Boolean variable’s value, you must write an IF statement, as in:
IF l_student_is_registered
Now, isn’t that silly? And a big waste of your time? Fortunately, it is very easy to fix this problem. Just build your own package, with lots of overloadings, on top of DBMS_OUTPUT.PUT_LINE. Here is a very abbreviated example of such a package. You can extend it easily, as I do with the p.l procedure (why type all those characters just to say “show me,” right?). A portion of the package specification is shown here:

   /* Display a string. */

   /* Display a string and then a Boolean value. */
      stg    IN   VARCHAR2,
      bool   IN   BOOLEAN
END do;
This package simply sits on top of DBMS_OUTPUT.PUT_LINE and enhances it. With p.l, I can now display a Boolean value without writing my own IF statement, as in:
   v_is_valid BOOLEAN :=
      book_info.is_valid_isbn ('5-88888-66');
   p.l (v_is_valid);
Would you like to use the p package? No problem! It's available on LiveSQL - download and install it in your environment and make your life just a little bit easier with overloading!

Restrictions on Overloading

There are several restrictions on how you can overload programs.

When the PL/SQL engine compiles and runs your program, it has to be able to distinguish between the different overloaded versions of a program; after all, it can’t run two different modules at the same time.

So when you compile your code, PL/SQL will reject any improperly overloaded modules. It cannot distinguish between the modules by their names because by definition they are the same in all overloaded programs.

Instead, PL/SQL uses the parameter lists of these sibling programs to determine which one to execute and/or the types of the programs (procedure versus function). As a result, the following restrictions apply to overloaded programs:

The datatype “family” of at least one of the parameters of overloaded programs must differ

INTEGER, REAL, DECIMAL, FLOAT, etc., are NUMBER subtypes. CHAR, VARCHAR2, and LONG are character subtypes. If the parameters differ only by datatype within the supertype or family of datatypes, PL/SQL does not have enough information to determine the appropriate program to execute. Note that there is an exception when it comes to some numeric datatypes, which is explored below.

Overloaded programs with parameter lists that differ only by name must be called using named notation

If you don’t use the name of the argument, how can the compiler distinguish between calls to two overloaded programs? Please note, however, that it is always risky to use named notation as an enforcement paradigm. You should avoid situations where named notation yields different semantic meaning from positional notation.

The parameter list of overloaded programs must differ by more than parameter mode

Even if a parameter in one version is IN and that same parameter in another version is IN OUT, PL/SQL cannot tell the difference at the point at which the program is called.

All of the overloaded programs must be defined within the same PL/SQL scope or block

You cannot define one "version" in one block (anonymous block, standalone procedure or function, or package) and define another subprogram with the same name in a different block. Oh wait, yes you can. :-) But they won't be overloading. They will be subprograms that happen to have the same name. The PL/SQL compiler will never look for a match outside of that scope. Either the subprogram inside the scope matches the invocation or you get a compilation error.

You also cannot overload two standalone programs; one simply replaces the other.

Overloaded functions must differ by more than their return type

At the time that the overloaded function is called, the compiler doesn’t know what type of data that function will return. The compiler therefore cannot determine which version of the function to use if all the parameters are the same.

Overloading with Numeric Types

While you cannot successfully overload two subprograms whose differences are CHAR vs VARCHAR2, you can overload two subprograms if their formal parameters differ only in numeric datatype. Consider the following block:
      DBMS_OUTPUT.PUT_LINE ('pls_integer version');

      DBMS_OUTPUT.PUT_LINE ('number version');
   proc1 (1.1);
   proc1 (1);
When I run this code, I see the following results:

number version
pls_integer version

The PL/SQL compiler is able to distinguish between the two calls. Notice that it called the “number version” when I passed a noninteger value. That’s because PL/SQL looks for numeric parameters that match the value, and it follows this order of precedence in establishing the match: it starts with PLS_INTEGER or BINARY_INTEGER, then NUMBER, then BINARY_FLOAT, and finally BINARY_DOUBLE. It will use the first overloaded program that matches the actual argument values passed.

While it is very nice that the database now offers this flexibility, be careful when relying on this very subtle overloading—make sure that it is all working as you would expect. Test your code with a variety of inputs and check the results. Remember that you can pass a string such as “156.4” to a numeric parameter; be sure to try out those inputs as well.

You can also qualify numeric literals and use conversion functions to make explicit which overloading (i.e., which numeric datatype) you want to call. If you want to pass 5.0 as a BINARY_FLOAT, for example, you could specify the value 5.0f or use the conversion function, TO_BINARY_FLOAT(5.0).

Watch out for Ambiguous Overloading!

If you go "overboard" with overloading (lots of combinations, not too carefully thought out), you could end up with a situation where it is hard to use those overloading. How do you know you've got a problem with your overloading? PLS-00307 will raise its ugly head.

Here's an example: I overload two procedures, whose only difference is the datatype: CHAR vs VARCHAR2.
   PROCEDURE calc_total (reg_in IN CHAR);

   PROCEDURE calc_total (reg_in IN VARCHAR2);

END salespkg; 

CREATE OR replace PACKAGE BODY salespkg
   PROCEDURE calc_total (reg_in IN CHAR)

   PROCEDURE calc_total (reg_in IN VARCHAR2)

END salespkg; 
It turns out that this is a rather interesting case. The package compiles without errors, but you cannot invoke either of these procedures. They are just too darn similar.
   salespkg.calc_total ('reg11');  

PLS-00307: too many declarations of 'CALC_TOTAL' match this call 
And the problem can be more subtle than this, because parameters can have default values. At a glance the parameter lists of your overloaded subprograms look quite different. But depending on how they are called....
   PROCEDURE calc_total (zone_in IN CHAR, date_in in DATE DEFAULT SYSDATE);  
   PROCEDURE calc_total (zone_in IN VARCHAR2);  
END salespkg; 

   salespkg.calc_total ('reg11');  

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

This LiveSQL script demonstrates the problems of ambiguous overloading.

Bottom line: make sure your overloading are sufficiently different to avoid ambiguity. This is unlikely to be a problem for most developers, but if you get all excited about overloading, you might over-do it. And end up with problems like these.

Thanks, O'Reilly Media!

Portions of this post have been lifted (gently, carefully, lovingly) from Oracle PL/SQL Programming, with permission from O'Reilly Media.


  1. Why now is valid this "why type all those characters just to say “show me,” right?" Ok, may be not to write DBMS_OUT.......... and etc. but what about my_output.put_line. I mean, there is certain principles we should not forget like make the code readable. Always appreciate your writings. See you


Post a Comment

Popular posts from this blog

Get rid of mutating table trigger errors with the compound trigger

When something mutates, it is changing. Something that is changing is hard to analyze and to quantify. A mutating table error (ORA-04091) occurs when a row-level trigger tries to examine or change a table that is already undergoing change (via an INSERT, UPDATE, or DELETE statement). In particular, this error occurs when a row-level trigger attempts to read or write the table from which the trigger was fired. Fortunately, the same restriction does not apply in statement-level triggers.

In this post, I demonstrate the kind of scenario that will result in an ORA-04091 errors. I then show the "traditional" solution, using a collection defined in a package. Then I demonstrate how to use the compound trigger, added in Oracle Database 11g Release1,  to solve the problem much more simply.

All the code shown in this example may be found in this LiveSQL script.
How to Get a Mutating Table ErrorI need to implement this rule on my employees table:
Your new salary cannot be more than 25x th…

How to Pick the Limit for BULK COLLECT

This question rolled into my In Box today:
In the case of using the LIMIT clause of BULK COLLECT, how do we decide what value to use for the limit? First I give the quick answer, then I provide support for that answer

Quick Answer
Start with 100. That's the default (and only) setting for cursor FOR loop optimizations. It offers a sweet spot of improved performance over row-by-row and not-too-much PGA memory consumption.Test to see if that's fast enough (likely will be for many cases).If not, try higher values until you reach the performance level you need - and you are not consuming too much PGA memory. Don't hard-code the limit value: make it a parameter to your subprogram or a constant in a package specification.Don't put anything in the collection you don't need. [from Giulio Dottorini]Remember: each session that runs this code will use that amount of memory.Background

When you use BULK COLLECT, you retrieve more than row with each fetch, reducing context switchi…

Quick Guide to User-Defined Types in Oracle PL/SQL

A Twitter follower recently asked for more information on user-defined types in the PL/SQL language, and I figured the best way to answer is to offer up this blog post.

PL/SQL is a strongly-typed language. Before you can work with a variable or constant, it must be declared with a type (yes, PL/SQL also supports lots of implicit conversions from one type to another, but still, everything must be declared with a type).

PL/SQL offers a wide array of pre-defined data types, both in the language natively (such as VARCHAR2, PLS_INTEGER, BOOLEAN, etc.) and in a variety of supplied packages (e.g., the NUMBER_TABLE collection type in the DBMS_SQL package).

Data types in PL/SQL can be scalars, such as strings and numbers, or composite (consisting of one or more scalars), such as record types, collection types and object types.

You can't really declare your own "user-defined" scalars, though you can define subtypes from those scalars, which can be very helpful from the perspective…