Introduction:

One of the most powerful aspects of the package is the ability to overload program units. When you overload, you define more than one program with the same name. These programs will differ in other ways (usually the number and types of parameters) so that at runtime the PL/SQL engine can figure out which of the programs to execute. You can take advantage of the overloading feature of packages to make your package-based features as accessible as possible.

Does overloading sound unfamiliar or strange? Well, have you ever used the TO_CHAR function? If so, then you have already been enjoying the creature comforts of overloading. TO_CHAR converts both numbers and dates to strings. Have you ever wondered why you don’t have to call functions with names like TO_CHAR_FROM_DATE or TO_CHAR_FROM_NUMBER? Probably not. You probably just took TO_CHAR for granted, and that is how it should be.

In reality, there are two different TO_CHAR functions (both defined in the STANDARD package): one to convert dates and another to convert numbers. The reason that you don’t have to care about such details and can simply execute TO_CHAR is that the PL/SQL runtime engine examines the kind of data you pass to TO_CHAR and then automatically figures out which of the two functions (with the same name) to execute. It’s like magic, only it’s better than magic: its intelligent software!

When you build overloaded modules, you spend more time in design and implementation than you might with separate, standalone modules. This additional up-front time will be repaid handsomely down the line in program productivity and ease of use.

1. When to Overload?

When you overload, you take the first step towards providing a declarative interface to PL/SQL-based functionality. With a declarative approach, a developer does not write a program to obtain the necessary functionality. Instead, she describes what she wants and lets the underlying code handle the details (this follows the approach used by the SQL language). The process of overloading involves abstracting out from separate programs into a single action.

Here are some of the circumstances that cause the PL/SQL fairy to whisper in my ear “Overload, overload…”

• Apply the same action to different kinds or combinations of data.
• Allow developers to use a program in the most natural and intuitive fashion; you use overloading to fit your program to the needs of the user.
• Make it easy for developers to specify, unambiguously and simply, the kind of action desired.

I explore these circumstances in the following sections.

1.1. Supporting many data combinations

This is probably the most common reason to employ overloading. The p package of PL/Vision (see the following sidebar) offers an excellent example of this kind of overloading opportunity. This package contains eight overloading’s of the l procedure so that you can pass many different combinations of data and have the package interpret and display the information properly. The following headers show, for example, a simplified portion of the specification for the p package, which illustrates the overloading:

PROCEDURE l (date_in IN DATE, mask_in IN VARCHAR2 := PLV.datemask);

PROCEDURE l (char_in IN VARCHAR2, number_in IN NUMBER);

PROCEDURE l (boolean_in IN BOOLEAN);

Because of my extensive overloading, I can pass a complex date expression (taking me back 18 years) and see the date and time in a readable format with a minimum of effort:

SQL> exec p.l(ADD_MONTHS(SYSDATE,-316));

SQL> exec p.l (‘a’ IN (‘d’, ‘e’, ‘f’));

1.2 Fitting the program to the user

Does the idea of fitting a program to your user sound odd or unnecessary? If so, change your attitude. We write our software to be used, to help others get their jobs done more easily or more efficiently. You should always be on the lookout for ways to improve your code so that it responds as closely as possible to the needs of your users. Overloading offers one way to achieve a very close fit.

You may sometimes end up with several overloading’s of the same program because developers will be using the program in different ways. In this case, the overloading does not provide a single name for different activities, so much as providing different ways of requesting the same activity. Consider the overloading for the PLVlog.put_line (shown in simplified form below):

PROCEDURE put_line

( Context_in IN VARCHAR2,

code_in IN INTEGER,

string_in IN VARCHAR2 := NULL,

create_by_in IN VARCHAR2 := USER

);

PROCEDURE put_line (string_in IN VARCHAR2);

The first header is the low-level version of put_line. It allows you to specify a full set of arguments to the program, including the context, the code, a string and the Oracle account providing the information. The second header asks only for the string, the text to be logged. What happened to all the other arguments? I suppressed them, because I found that in many situations a user of PLVlog simply doesn’t care about all of those arguments. He simply wants to pass it a string to be saved. So rather than make him enter dummy values for all the unnecessary data, I provide a simpler interface, which in turn calls the low-level put_line with its own dummy values

1.3 Unambiguous, simple arguments

A less common application of overloading offers a way for developers to specify very easily which of the overloaded programs should be executed. The best way to explain this technique is with an example. The PLVgen package allows you to generate PL/SQL source code, including procedures, functions, and packages. Let’s consider how to request the generation of a function.

A function has a data type: the type of data returned by the function. So when you generate a function, you want to be able to specify whether it is a number function, string function, date function, etc. If I ignored overloading, I might offer a package specification like this:

PACKAGE PLVgen

IS

PROCEDURE stg_func (name_in IN VARCHAR2);

PROCEDURE num_func (name_in IN VARCHAR2);

PROCEDURE date_func (name_in IN VARCHAR2);

END;

to name just a few. Of course, this means that a user of PLVgen must remember all of these different program names. Is it num or nbr? Stg or strg or string? Why use the four-letter date when the others are just three letters? Wow! That is very confusing. Let’s try overloading of the kind previously encountered in this chapter. I will declare a named constant for each kind of data and then, well, it would seem that I really only need one version of the func procedure:

PACKAGE PLVgen

IS

stg CONSTANT VARCHAR2(1) := ‘S’;

num CONSTANT VARCHAR2(1) := ‘N’;

dat CONSTANT VARCHAR2(1) := ‘D’;

PROCEDURE func (name_in IN VARCHAR2, type_in IN VARCHAR2);

END;

I could then generate a numeric function as follows:

SQL> exec PLVgen.func (‘booksales’, PLVgen.num);

Now, I still need to know the names of the constants, so it is pretty much the same situation as we encountered in my first func attempt. Furthermore, I would like to be able to pass a default value to be returned by the generated function, so I really would need to overload as shown in the next iteration:

PACKAGE PLVgen

IS

stg CONSTANT VARCHAR2(1) := ‘S’;

num CONSTANT VARCHAR2(1) := ‘N’;

dat CONSTANT VARCHAR2(1) := ‘D’;

PROCEDURE func(name_in IN VARCHAR2, type_in IN VARCHAR2, defval_in IN VARCHAR2);

PROCEDURE func(name_in IN VARCHAR2, type_in IN VARCHAR2, defval_in IN NUMBER);

PROCEDURE func(name_in IN VARCHAR2, type_in IN VARCHAR2, defval_in IN DATE);

END;

Might there not be a simpler way to handle this? Notice that the second parameter is a way for the user to specify the datatype of the function. You pass in a string constant, and PLVgen uses an IF statement to determine which constant you have provided. Why not skip the constant and simply pass in data itself of the right type? Then the PL/SQL runtime engine itself would automatically perform the conditional logic to determine which program to run, which code to execute. Consider this next version of the PLVgen package specification:

PACKAGE PLVgen

IS

PROCEDURE func (name_in IN VARCHAR2, type_in IN VARCHAR2, defval_in IN VARCHAR2);

PROCEDURE func (name_in IN VARCHAR2, type_in IN NUMBER, defval_in IN NUMBER);

PROCEDURE func (name_in IN VARCHAR2, type_in IN DATE, defval_in IN DATE);

END;

2. Benefits of Overloading:

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. This process transfers 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 PL/SQL tables. Instead, you simply tell the compiler that you want to add and pass it the value you want added. PL/SQL and your overloaded programs figure out what you want to do and they do it for you.

When you build overloaded modules, you spend more time in design and implementation than you might with separate, standalone modules. This additional up-front time will be repaid handsomely down the line. You and others will find that it is easier and more efficient to use your programs.

 3. Where to Overload Modules:

There are only two places in PL/SQL programs where you can overload modules:

• Inside the declaration section of a PL/SQL block
• Inside a package

You cannot, in other words, overload the names of standalone programs, nor can you create two completely independent modules with the same name but different parameter lists. For example if you attempt from SQL*Plus to “create or replace” the following two versions of chg_estimate, the second try will fail, as shown below:

CREATE OR REPLACE PROCEDURE chg_estimate (date_in IN DATE) IS

BEGIN

NULL;

END chg_estimate;

/

Procedure created.

CREATE OR REPLACE FUNCTION chg_estimate (dollars_in IN NUMBER)

RETURN NUMBER

IS

BEGIN

NULL;

END chg_estimate;

ORA-0955: name is already used by an existing object

Because the name was already used for a procedure, PL/SQL rejected the attempt to replace it with a function. The compiler would not interpret this second “create or replace” as an effort to create a second module of the same name.

Modules must be overloaded within a particular context. A PL/SQL block provides a scope: anything declared in its declaration section can only be accessed within the execution and exception sections. A PL/SQL package also provides a context for the overloaded modules.

4. 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 name, because by definition that is the same in all overloaded programs. Instead, PL/SQL uses the parameter lists of these sibling programs to determine which one to execute. 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 with which to determine the appropriate program to execute. As a result, the following programs cannot be overloaded:

FUNCTION calculate_net_profit (revenue_in IN POSITIVE)

RETURN NUMBER IS

BEGIN

NULL;

END calculate_net_profit;

FUNCTION calculate_net_profit (revenue_in IN BINARY_INTEGER)

RETURN NUMBER IS

BEGIN

NULL;

END calculate_net_profit;

Nor can these programs be successfully overloaded, since CHAR and VARCHAR2 are both character datatypes:

PROCEDURE trim_and_center (string_in IN CHAR) IS

BEGIN

NULL;

END;

PROCEDURE trim_and_center (string_in IN VARCHAR2) IS

BEGIN

NULL;

END;

5. Conclusion:

Just remember the best practice with overloading requires you do two things. Make signatures unique by the number of mandatory parameters in any parameter list. Make one mandatory parameter name unique when two or more functions or procedures have the same number and type of parameters

The benefits and the beauty of overloading can be appreciated fully only by using overloaded programs — and then in most cases, you won’t even notice, because overloading hides the underlying complexity so you can concentrate on more important issues. You will, I hope, get a sense of the value of overloading from using — and perhaps even extending — PL/Vision. Do take some time to pursue the various spb files (the package bodies) and examine the many different examples of overloading you will find there.

The more you overload your packaged procedures and functions, the more functionality you offer to your users. Where overloading is appropriate, it is also impossible to overdo your overloading. If you see another interesting and useful combination, if you see a way to simplify the way a user passes information to your package, then overload for it! It will always be the right thing to do; your biggest challenge will be in figuring out how to implement all these overloading’s in a modular and maintainable fashion.