Tuesday, August 29, 2006

Well I never

Good to be reminded that there's always something that you don't already know. And that's especially true of Oracle. I'd never suspected that some of those things are pretty fundamental, like the fact that package functions and procedures can be overloaded! I'd always assumed that since standalone functions and procedures can't, that the same was true of packages. Turns out that assumption was all wrong...

I.E.
This doesn't work:

CREATE FUNCTION cannot_be_overloaded RETURN NUMBER IS
BEGIN
RETURN 0;
END cannot_be_overloaded;
/

CREATE FUNCTION cannot_be_overloaded ( pc_varchar VARCHAR2 ) RETURN VARCHAR2 IS
BEGIN
RETURN pc_varchar;
END cannot_be_overloaded;
/


But this does!

CREATE OR REPLACE PACKAGE test_pkg IS
--
FUNCTION can_be_overloaded RETURN NUMBER;
FUNCTION can_be_overloaded ( pc_varchar VARCHAR2 ) RETURN VARCHAR2;
--
END test_pkg;
/

CREATE OR REPLACE PACKAGE BODY test_pkg IS
--
FUNCTION can_be_overloaded RETURN NUMBER IS
BEGIN
RETURN 0;
END can_be_overloaded;
--
FUNCTION can_be_overloaded ( pc_varchar VARCHAR2 ) RETURN VARCHAR2 IS
BEGIN
RETURN pc_varchar;
END can_be_overloaded;
--
END test_pkg;
/


I'm sure there are gotchas in there, and I'm not really sure it's actually that useful (I've gone 8 years without it ;-) ), but still... how did I miss it? What else have I missed?

Update - aside: Why does Oracle allow overloading in the package, but not with standalone. I'm guessing, but probably because not allowing the standalone overload makes things like the 'DROP PROCEDURE' command a lot simpler to use (care to specify which procedure with that name to drop?)
Probably because allowing the package procedures to be overloaded seemed like a god idea to someone in Oracle ;-o

Update: Just re-reading the bulk of the post... and now I'm blogging something that I've not tested (no Oracle at home). But I reckon that the following won't work:


CREATE OR REPLACE PACKAGE test_pkg IS
--
FUNCTION cannot_be_overloaded RETURN NUMBER;
FUNCTION cannot_be_overloaded RETURN VARCHAR2;
--
END test_pkg;
/

CREATE OR REPLACE PACKAGE BODY test_pkg IS
--
FUNCTION cannot_be_overloaded RETURN NUMBER IS
BEGIN
RETURN 0;
END cannot_be_overloaded;
--
FUNCTION cannot_be_overloaded RETURN VARCHAR2 IS
BEGIN
RETURN 'Character';
END cannot_be_overloaded;
--
END test_pkg;
/


Any calls to the functions would be ambiguous. Surely Oracle can't choose which one to use based on the type of the variable you're going to hold the value in... that would be a nightmare bit of compiler to implement. No no no!

Also, I reckon you'd have to take care with this:


CREATE OR REPLACE PACKAGE test_pkg IS
--
FUNCTION can_be_overloaded ( pn_number NUMBER ) RETURN NUMBER;
FUNCTION can_be_overloaded ( pc_varchar VARCHAR2 ) RETURN VARCHAR2;
--
END test_pkg;
/

CREATE OR REPLACE PACKAGE BODY test_pkg IS
--
FUNCTION can_be_overloaded ( pn_number NUMBER ) RETURN NUMBER IS
BEGIN
RETURN pn_number;
END can_be_overloaded;
--
FUNCTION can_be_overloaded ( pc_varchar VARCHAR2 ) RETURN VARCHAR2 IS
BEGIN
RETURN pc_varchar;
END can_be_overloaded;
--
END test_pkg;
/


Even if the above would compile (I don't know if it would), then if you were to call the above with:

test_pkg.can_be_overloaded ( '100' );

I suspect that Oracle will throw a wobbler. The parameter passed could be treated as a number or a varchar2, meaning either function could be valid for passing.
The only reason I suspect the package would compile is that the call could become non-ambiguous with:

test_pkg.can_be_overloaded ( pc_varchar => '100' );


I can see how this could get very knotty, positional parameters could make all kinds of function calls ambiguous.
Reckon I might try some experiments tomorrow...

11 comments:

SydOracle said...

Just tried, and the examples compile okay, but when called they throw an exception :
PLS-00307: too many declarations of 'CANNOT_BE_OVERLOADED' match this call
Interestingly, even the PL/SQL precompiler warnings in 10GR2 don't throw out any message.

Anonymous said...

Check out:

describe sys.standard;

Most of your everday Oracle functions are actually overloaded package functions.

Rob Baillie said...

That makes sense... ever tried to call TO_CHAR passing a CHAR value? I seem to remember it often (if not always) throws a 'too many declarations' exception.
Going to try to experiment with this later today, see if anything interesting comes up.

Anonymous said...

I think this is more useful than you might think. I've written quite a few overloaded packages in my time to stop people from having to think about the datatypes they're passing.

It all makes sense really - because you're creating the package spec up-front, Oracle has a signature for the different variants contained in the one object. Choosing different discrete procedures based on their spec wouldn't be as straightforward.

When I was teaching the PL/SQL course, I always used to use dbms_output as an example, because everyone's probably used that without even thinking about it ;-)

Anonymous said...

In most cases you can get around the too many declarations by using named parameters like this:

test_pkg.can_be_overloaded(pc_varchar => 'test')

I always use name parameters now a days.

Regards Pete Lorenzen

William Robertson said...

The example runs fine for me (10.1, htmldb.oracle.com). This worked:

DECLARE
v NUMBER;
c VARCHAR2(10);
BEGIN
v := test_pkg.can_be_overloaded(123);
c := test_pkg.can_be_overloaded(123);
END;

AFAIK the TO_CHAR "too many declarations" thing should not be a problem after around 8i.

I'm not sure DBMS_OUTPUT is such a great example of overloading. The VARCHAR2 version of PUT_LINE goes

tmpbuf := tmpbuf || a;

while the NUMBER and DATE versions go

tmpbuf := tmpbuf || to_char(a);

btw Rob, what are the empty comment lines for? Apart from defeating paragraph selection in Vim of course.

Rob Baillie said...

Excellent stuff! I'd never thought to post about something I wasn't sure about and have other people do the finding out behaviour for me before. I'm absolutely loving the responses!

It's pretty obvious I've got some experimenting to do in order to work out for myself what's possible and what's not.

As for the single line comments, it's a formatting throwback from my first job. It was just part of their standards and I've always liked the way it looks. I think it helps you to spot the indentation patterns, and where you've gone wrong. But truth be told it's really just because I think it lks nice!

Anonymous said...

"I'm not sure DBMS_OUTPUT is such a great example of overloading. The VARCHAR2 version of PUT_LINE goes"

Agreed - from the coding point of view, but it's a very easy example for people to understand the external interface to.

William Robertson said...

I have a feeling that prior to 8i, or perhaps even earlier, blank lines used to get stripped from user_source, making it hard to trace errors from the line numbers in the error stack, and that is why some people used to add blank comment lines.

The thing with DBMS_OUTPUT.PUT_LINE as an example of overloading is that it does the same thing it would have done if there was only a VARCHAR2 version, i.e. without overloading, so I can imagine people thinking, what's the point of that?

I'm still waiting for Oracle to get around to overloading the aggregate functions to handle intervals.

Rob Baillie said...

I'm pretty much with William on this, I suggest that I'd never noticed it was overloaded precisely because it doesn't appear to actually change its behaviour.
But then that's the point of overloading most of the time isn't it? Oracle's pretty good at doing implicit casts noawadays, so do we really need to do the explicit casts ourselves? Of course there are times when you might want to change the validation of parameters based in the data type though I've never actaully needed to do this ina bit of code I've written.

The only other reason I've seen it being used is in Java. That language allows it because you can't default (or at least couldn't default parameters), but you can in Oracle.

It might also be more useful if there was no named parameter notation. That way you wouldn't be able to skip defaulted parameters to set later ones.

Of course, without it TO_CHAR would be pretty damn difficult to implement. It might just be that I'v not implemented enough code that's truly fundamental or generic. So maybe I'm just playing devil's advocate...

Anonymous said...

probably because not allowing the standalone overload makes things like the 'DROP PROCEDURE' command a lot simpler to use (care to specify which procedure with that name to drop?)

As a further aside, PostgreSQL allows overloading of standlone procedures and when you want to drop them, you specify the argument types along with the procedure name.

But there again, people who install lots of standalone procedures willy nilly should be hit on the nose with a rolled up newspaper for not using packages.

As a final aside, if you've never installed a procedure willy nilly, get a test schema set up and do it. It's lots of fun although you will feel guilty.