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

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.

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.

Restrictions

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:

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

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:

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:

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

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

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.