Well I Never - Followup 1

OK, so I've managed to grab some time during the day to experiment, and I've got things to post. For now I've just got the time for this...

Turns out that William Robertson was quite right, the TO_CHAR 'too many declarations' issue has gone away (certainly by the time it reached 9.2), and I never even noticed!

SQL*Plus: Release 9.2.0.1.0 - Production on Thu Aug 31 13:53:25 2006

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Release 9.2.0.6.0 - Production
JServer Release 9.2.0.6.0 - Production

SQL> SELECT TO_CHAR( 'CHARACTER' ) FROM DUAL
  2  /

TO_CHAR('
---------
CHARACTER

SQL> SELECT TO_CHAR( NULL ) FROM DUAL
  2  /

T
-


SQL>

Second up (also in 9.2) the first suspicion I had was quite right... the following doesn't work.

SQL*Plus: Release 9.2.0.1.0 - Production on Thu Aug 31 13:42:49 2006

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Release 9.2.0.6.0 - Production
JServer Release 9.2.0.6.0 - Production

SQL> CREATE OR REPLACE PACKAGE test_pkg IS
  2    --
  3    FUNCTION cannot_be_overloaded RETURN NUMBER;
  4    FUNCTION cannot_be_overloaded RETURN VARCHAR2;
  5    --
  6  END test_pkg;
  7  /

Package created.

SQL>
SQL> CREATE OR REPLACE PACKAGE BODY test_pkg IS
  2    --
  3    FUNCTION cannot_be_overloaded RETURN NUMBER IS
  4    BEGIN
  5      RETURN 0;
  6    END cannot_be_overloaded;
  7    --
  8    FUNCTION cannot_be_overloaded RETURN VARCHAR2 IS
  9    BEGIN
 10      RETURN 'Character';
 11    END cannot_be_overloaded;
 12    --
 13  END test_pkg;
 14  /

Package body created.

SQL> SELECT test_pkg.cannot_be_overloaded FROM DUAL
  2  /
SELECT test_pkg.cannot_be_overloaded FROM DUAL
       *
ERROR at line 1:
ORA-06553: PLS-307: too many declarations of 'CANNOT_BE_OVERLOADED' match this
call

SQL> DECLARE
  2    vn_number  NUMBER;
  3    vc_character  VARCHAR2(100);
  4  BEGIN
  5    vn_number := test_pkg.cannot_be_overloaded;
  6    vc_character := test_pkg.cannot_be_overloaded;
  7  END;
  8  /
  vn_number := test_pkg.cannot_be_overloaded;
                        *
ERROR at line 5:
ORA-06550: line 5, column 25:
PLS-00307: too many declarations of 'CANNOT_BE_OVERLOADED' match this call
ORA-06550: line 5, column 3:
PL/SQL: Statement ignored
ORA-06550: line 6, column 28:
PLS-00307: too many declarations of 'CANNOT_BE_OVERLOADED' match this call
ORA-06550: line 6, column 3:
PL/SQL: Statement ignored

SQL>

However, my second suspicion was off the mark (at least in 9.2). Almost certainly this is related to the change in behaviour to TO_CHAR described above,

SQL*Plus: Release 9.2.0.1.0 - Production on Thu Aug 31 13:45:47 2006

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Release 9.2.0.6.0 - Production
JServer Release 9.2.0.6.0 - Production

SQL> CREATE OR REPLACE PACKAGE test_pkg IS
  2    --
  3    FUNCTION can_be_overloaded ( pn_number   NUMBER   ) RETURN NUMBER;
  4    FUNCTION can_be_overloaded ( pc_varchar  VARCHAR2 ) RETURN VARCHAR2;
  5    --
  6  END test_pkg;
  7  /

Package created.

SQL>
SQL> CREATE OR REPLACE PACKAGE BODY test_pkg IS
  2    --
  3    FUNCTION can_be_overloaded ( pn_number   NUMBER   ) RETURN NUMBER IS
  4    BEGIN
  5      RETURN pn_number;
  6    END can_be_overloaded;
  7    --
  8    FUNCTION can_be_overloaded ( pc_varchar  VARCHAR2 ) RETURN VARCHAR2 IS
  9    BEGIN
 10      RETURN pc_varchar;
 11    END can_be_overloaded;
 12    --
 13  END test_pkg;
 14  /

Package body created.

SQL> SELECT test_pkg.can_be_overloaded( 0 ) FROM DUAL
  2  /

TEST_PKG.CAN_BE_OVERLOADED(0)
-----------------------------
                            0

SQL> SELECT test_pkg.can_be_overloaded( 'WORD' ) FROM DUAL
  2  /

TEST_PKG.CAN_BE_OVERLOADED('WORD')
--------------------------------------------------------------------------------
WORD

SQL> SELECT test_pkg.can_be_overloaded( '100' ) FROM DUAL
  2  /

TEST_PKG.CAN_BE_OVERLOADED('100')
--------------------------------------------------------------------------------
100

SQL> DECLARE
  2    vn_number     NUMBER;
  3    vc_character  VARCHAR2(100);
  4  BEGIN
  5    vn_number     := test_pkg.can_be_overloaded( 0 );
  6    vc_character  := test_pkg.can_be_overloaded( 'WORD' );
  7    vc_character  := test_pkg.can_be_overloaded( '0' );
  8    vn_number     := test_pkg.can_be_overloaded( TO_NUMBER( '0' ) );
  9    vn_number     := test_pkg.can_be_overloaded( TO_CHAR( 0 ) );
 10  END;
 11  /

PL/SQL procedure successfully completed.

SQL>

Cheers to everyone who commented on the last post... it's led me to check out a few things that I might not have bothered with and I reckon I'll be looking a little deeper in the next few days. Contrived examples of where named parameter notation could go wrong are called for I think ;-)

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...