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