Can I make PL/SQL functions more strongly typed?

Question

I like to use overloading to speed up development, and I want to structure my code to make future changes easier. I have a function that accepts a NUMBER or a VARCHAR2, but I want it to throw a compilation error if I pass in a DATE.

Here's an illustration of the problem I want to avoid:

DECLARE
   PROCEDURE do_something(
      in_ IN VARCHAR2);

   PROCEDURE do_something(
      in_ IN NUMBER);

   mystery_variable_1 VARCHAR2(32767);
   mystery_variable_2 NUMBER;
   mystery_variable_3 DATE;

   PROCEDURE do_something(
      in_ IN VARCHAR2)
   IS
   BEGIN
      dbms_output.put_line('do_something called with VARCHAR2 argument ' || in_);
   END do_something;

   PROCEDURE do_something(
      in_ IN NUMBER)
   IS
   BEGIN
      dbms_output.put_line('do_something called with NUMBER argument ' || in_);
   END do_something;
BEGIN
   mystery_variable_1 := 'Hello, World!';
   mystery_variable_2 := 42;
   mystery_variable_3 := TO_DATE('2063-04-05', 'YYYY-MM-DD');

   do_something(mystery_variable_1);
   do_something(mystery_variable_2);
   do_something(mystery_variable_3); -- I want this to throw a compilation error.
END;
/

Here's the output showing the implicit cast from DATE to VARCHAR2:

do_something called with VARCHAR2 argument Hello, World!
do_something called with NUMBER argument 42
do_something called with VARCHAR2 argument 05-APR-63

I don't want that third line to be allowed.

The best work-around I have so far is to create a honeypot overload that throws an application error. This catches the issue in testing but not at compile time.

DECLARE
   PROCEDURE do_something(
      in_ IN VARCHAR2);

   PROCEDURE do_something(
      in_ IN NUMBER);

   PROCEDURE do_something(
      in_ IN DATE);

   mystery_variable_1 VARCHAR2(32767);
   mystery_variable_2 NUMBER;
   mystery_variable_3 DATE;

   PROCEDURE do_something(
      in_ IN VARCHAR2)
   IS
   BEGIN
      dbms_output.put_line('do_something called with VARCHAR2 argument ' || in_);
   END do_something;

   PROCEDURE do_something(
      in_ IN NUMBER)
   IS
   BEGIN
      dbms_output.put_line('do_something called with NUMBER argument ' || in_);
   END do_something;

   PROCEDURE do_something(
      in_ IN DATE)
   IS
   BEGIN
      RAISE_APPLICATION_ERROR(-20001, 'do_something called with DATE argument ' || TO_CHAR(in_, 'YYYY-MM-DD HH24:MI:SS'));
   END do_something;

BEGIN
   mystery_variable_1 := 'Hello, World!';
   mystery_variable_2 := 42;
   mystery_variable_3 := TO_DATE('2063-04-05', 'YYYY-MM-DD');

   do_something(mystery_variable_1);
   do_something(mystery_variable_2);
   do_something(mystery_variable_3);
END;
/

If I leave this work-around in place for the benefit of the maintenance developer, I get an unsightly PLW-06006 error when my private function is excluded from my package.

Can I trap this at compile time instead?

Answer 1

I think you are fighting against how Oracle works with implicit type conversion but you could suppress that conversion with custom types. Looks ugly but kind of does what you asked:

drop type vtype;
drop type ntype;

create type vtype as object(vvar varchar2(32767));
/

create type ntype as object(nvar NUMBER);
/

DECLARE
   PROCEDURE do_something(
      in_ IN vtype);

   PROCEDURE do_something(
      in_ IN ntype);

   mystery_variable_1 vtype;
   mystery_variable_2 ntype;
   mystery_variable_3 DATE;

   PROCEDURE do_something(
      in_ IN vtype)
   IS
   BEGIN
      dbms_output.put_line('do_something called with vtype argument ' || in_.vvar);
   END do_something;

   PROCEDURE do_something(
      in_ IN ntype)
   IS
   BEGIN
      dbms_output.put_line('do_something called with ntype argument ' || in_.nvar);
   END do_something;
BEGIN
   mystery_variable_1.vvar := 'Hello, World!';
   mystery_variable_2.nvar := 42;
   mystery_variable_3 := TO_DATE('2063-04-05', 'YYYY-MM-DD');

   do_something(mystery_variable_1);
   do_something(mystery_variable_2);
   do_something(mystery_variable_3); -- I want this to throw a compilation error.
END;
/

Output:

 30     do_something(mystery_variable_1);
 31     do_something(mystery_variable_2);
 32     do_something(mystery_variable_3); -- I want this to throw a compilation error.
 33  END;
 34  /
   do_something(mystery_variable_3); -- I want this to throw a compilation error.
   *
ERROR at line 32:
ORA-06550: line 32, column 4:
PLS-00306: wrong number or types of arguments in call to 'DO_SOMETHING'
ORA-06550: line 32, column 4:
PL/SQL: Statement ignored

Bobby