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 ;-)