Friday, 11 November 2016

Oracle Programming Basics: Overloading

It is probably a bad idea to define a word by using a bigger one, but I really can’t introduce the concept of overloading without briefly mentioning polymorphism. Polymorphism, in computer programming, is the ability of an object to interface in different ways depending on other, external criteria. And if that isn’t exactly clear, don’t worry about it; just know that overloading is a type of polymorphism. Store that fact somewhere in the back of your brain; you may never need it again.

Overloading is the act of creating multiple subprograms –  procedures or functions – with the same name.
But hey, wait a minute, everyone knows that Oracle identifiers must be unique. So how can we have more than one procedure, for example, with the same name?

Ah, that’s the clever thing about overloading, and the source of its power. While the procedures might have the same name, they must have different specs, different parameter lists.

An example might help, at this point.

PROCEDURE send_email (pRecipient VARCHAR2);

PROCEDURE send_email (pRecipient VARCHAR2, pSubject VARCHAR2);

PROCEDURE send_email (pRecipient VARCHAR2, pSendDate DATE);

Even though all 3 procedures share the same name – send_email – they can be overloaded because they expect different parameters. Subprograms can be overloaded as long as their parameters differ in number, datatype or order.

But Why

Even though you probably now see how overloading works, its usefulness might not yet be immediately apparent to you. But think about it; because the procedures do the same thing – send emails – it is logical to give them the same name, and handle any complexity in the background. So now, whenever you need to send an email you only (seemingly) have one procedure to call – send_email – rather than a confusing morass of similarly named procedures: send_email, send_email_with_subject, send_email_on_scheduled_date etc.


There are a few restrictions that you need to be aware of.

You can only overload local or packaged subprograms. What that means is that you can’t overload standalone procedures or functions. Sorry.

On second thoughts, I’m not sorry. I know this is a digression, but I really don’t like standalone subprograms. They’re like database litter, like last week’s socks and underwear lying all over your bedroom floor. Tidy them up, put them in packages.

Actually, there is a way in which you can ‘overload’ standalone functions.  It’s a little bit of a hack, but bear with me. Oracle allows you create user-defined operators, which is a means by which you can bind one or more functions to a single operator. That way, every time you use that operator in your DML, it will execute one of those functions, depending on the parameters you pass in.

I’ll show you what I mean. Assume we have the following two standalone functions:

FUNCTION get_group_size_using_name (pGroupName VARCHAR2) RETURN NUMBER;

FUNCTION get_group_size_using_id (pGroupId NUMBER) RETURN NUMBER;

We can now create an operator, using the following syntax:

    USING get_group_size_using_name,
    USING get_group_size_using_id;

Now, in your DML, you can use your ‘overloaded’ operator – group_size – and trust it to route your parameter to the right function, as follows:

FROM groups
WHERE group_size(group_name) = 15;

FROM groups
WHERE group_size(group_id) = 15;

And now that I’ve shown you how to overload standalone functions – even though you really shouldn’t create standalone functions – we can go back to talking about the restrictions on overloading.

Let’s stay with functions. You cannot overload non-standalone functions if the only difference between them is the return datatype. Which means you cannot overload the following:

FUNCTION is_email_valid (pEmailAddress VARCHAR2) RETURN BOOLEAN;

FUNCTION is_email_valid (pEmailAddress VARCHAR2) RETURN NUMBER;

You cannot overload subprograms if their formal parameters are different but belong to the same family type. Which means you cannot overload the following:

PROCEDURE create_group (pSize NUMBER);

PROCEDURE create_group (pID INTEGER);

You also cannot overload subprograms if their parameters only differ in mode. Which means you cannot overload the following:

PROCEDURE create_group (pID IN NUMBER);


And a final restriction, based on my practical experience. Overloading is useful, really useful, and sometimes it is the only sensible solution to a problem, but overloaded subprograms can be a pain in the backside to maintain. Imagine you’re the poor grunt tasked with rooting out a bug caused by a call to send_email.  How would you feel when you find out that, rather than a nice, single procedure, send_email is actually a hydra-headed monster of 10 different overloaded procedures? So whenever you create overloaded procedures, do remember that they are harder to maintain.