Last week I saw a PL/SQL teaser tweet by Steven Feurestein (@sfonplsql):

https://twitter.com/sfonplsql/status/583365900373377024

The question sounded simple: can you build a valid package with header and body with more than one procedure – that can compile but can never run. That was a really interesting question and I found myself thinking about it while working.

Since it is really had some good things we could learn from it, let’s go over my thought process and get to know this unique PL/SQL behavior.

When I first saw the question, I thought the question itself shouldn’t be very hard, but it also wasn’t very clear (as tweets often are). What does it mean “couldn’t run”? Does “fail at all attempts” count as “couldn’t run”?
I tried to understand the idea behind it and I had a couple of directions I could go on.

Privileges

My first direction is looking at the privileges perspective. Can we create a valid procedure for one user but it will be invalid to another?
The answer is yes. By using AUTHID CURRENT_USER we can create a procedure in our package that can be only run from one of the users due to lack in privileges. Why is that? Because when we use the default AUTHID DEFINER we say that the privileges are derived from the owner of the package. When we user CURRENT_USER we say that the privs derived from the runner of the package and he might not have privleges to use one of the underlying tables.
Example:

conn Zohar

create table zohar_test as select * From dual;

CREATE OR REPLACE PACKAGE MYTEST AUTHID CURRENT_USER
AS
        PROCEDURE MYTEST_1;
END MYTEST;
/

CREATE OR REPLACE PACKAGE body MYTEST  as 
        PROCEDURE MYTEST_1 is
			x number;
        BEGIN
            select 1 into x from zohar_test;
        END MYTEST_1;
END MYTEST;
/

grant execute on ZOHAR.MYTEST to public;

begin
        ZOHAR.MYTEST.MYTEST_1;
end;
/

And when we run it from a different user:

SQL> begin
  2    zohar.mytest.mytest_1;
  3  end;
  4  /
  zohar.mytest.mytest_1;
  *
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "ZOHAR.MYTEST", line 5
ORA-06512: at line 2 

But there is a problem here – this only fails because Zohar2 don’t have privileges to Zohar.zohar_test. If he had the privs, this would have worked so this doesn’t fit the question.

Dynamic SQL

The next think I thought of was using an invalid dynamic SQL inside the procedure. That will insure no one could run the procedure because it will always fail with the invalid command:

CREATE OR REPLACE PACKAGE MYTEST
AS
        PROCEDURE MYTEST_1;
END MYTEST;
/

CREATE OR REPLACE PACKAGE body MYTEST  as 
        PROCEDURE MYTEST_1 is
			x number;
        BEGIN
            execute immediate 'aaa';
        END MYTEST_1;
END MYTEST;
/

And when we run it:

SQL> begin
  2          ZOHAR.MYTEST.MYTEST_1;
  3  end;
  4  /
begin
*
ERROR at line 1:
ORA-00900: invalid SQL statement
ORA-06512: at "ZOHAR.MYTEST", line 5
ORA-06512: at line 2

This also not very good because the procedure had started running – it just failed during runtime.

At the point, it was clear: what we’re looking for is a compilation error cause by runtime. This could happen if we send the wrong parameter type (but we can avoid it by sending the right one) or using wrong number of parameters (also can be avoided by sending the right number).
At that point I looked really good and hard at the question. There was a huge hint there to direct me in the right way. The question stated that there should be more than one procedure in package. All my solutions so far had only one. Someone at twitter had suggested doing an endless loop but this is obviously wasn’t the answer.
And then it hit me!

Overloading

PL/SQL has an overloading properties. That means that when we write a pl/sql code (packages, in particular) we can have multiple definitions to the same procedure (or function) – if all the procedures has different parameters. This is mostly useful when changing interfaces between versions (adding or removing parameters) and we want to keep the old interface active. This is also useful when we can get multiple types to the same value and have different behavior based on the type we got (i.e. date, number, string etc.).
This is all excellent, but Oracle have multiple types that looks exactly the same – strings are the most common amongst them. When we get a string from a user we can never tell if he meant “CHAR”, “VARCHAR2” or “NVARCHAR”. It can also be a CLOB or LONG.

CREATE OR REPLACE PACKAGE MYTEST
AS
        PROCEDURE MYTEST_1 (p1 varchar2);
        PROCEDURE MYTEST_1 (p1 char);
END MYTEST;
/

CREATE OR REPLACE PACKAGE body MYTEST
AS
        PROCEDURE MYTEST_1 (p1 varchar2) IS
        BEGIN
                null;
        END MYTEST_1;

        PROCEDURE MYTEST_1 (p1 char) IS
        BEGIN
                null;
        END MYTEST_1;
END MYTEST;
/

begin
        MYTEST.MYTEST_1(p1 => 'a');
end;
/

And the result is:

SQL> begin
  2          MYTEST.MYTEST_1(p1 => 'a');
  3  end;
  4  /
        MYTEST.MYTEST_1(p1 => 'a');
        *
ERROR at line 2:
ORA-06550: line 2, column 9:
PLS-00307: too many declarations of 'MYTEST_1' match this call
ORA-06550: line 2, column 9:
PL/SQL: Statement ignored

This is exactly the thing Steven was asking about – we have multiple declarations that meets that call. Even if we created a variable that will actively state the type of the parameter, the database still won’t be sure which declaration it should use:

SQL> declare
  2     a char(1) := 'a';
  3  begin
  4          MYTEST.MYTEST_1(p1 => a);
  5  end;
  6  /
        MYTEST.MYTEST_1(p1 => a);
        *
ERROR at line 4:
ORA-06550: line 4, column 9:
PLS-00307: too many declarations of 'MYTEST_1' match this call
ORA-06550: line 4, column 9:
PL/SQL: Statement ignored

We would have expected that the database will know this is a wrong condition and stop us from messing around, but even if we use all the plsql warning, it won’t warn us about that:

SQL> alter session set plsql_warnings = 'ENABLE:ALL';
Session altered.
SQL> CREATE OR REPLACE PACKAGE MYTEST  AUTHID  definer
  2  AS
  3          PROCEDURE MYTEST_1 (p1 varchar2);
  4          PROCEDURE MYTEST_1 (p1 char);
  5  END MYTEST;
  6  /

Conclusion

This was a fun question and a great topic to write a post about even though I have so much in my backlog…. 🙂
Steven had also posted a video explaining this behiviour – I recommend watching it: