Don't test PL/SQL features with trivial code

On the one hand, when you test something, you want to keep your test code as simple as possible so that you can focus on the issue you are testing.

On the other hand, if you make your code too simple you might find yourself baffled at the resulting behavior.

Why? Because the PL/SQL compiler is just too darned smart.

Today, I got a DM on Twitter asking me why the package body below was compiling without any errors, even though he specified that the PLW-06009 warning should be treated as a compile error.

The code:

ALTER SESSION SET plsql_warnings = 'Error:6009';

CREATE OR REPLACE PACKAGE pkg_test AS
    PROCEDURE test_job (p_test_parameter IN OUT VARCHAR2);
END pkg_test;
/

CREATE OR REPLACE PACKAGE BODY pkg_test AS
    PROCEDURE test_job (p_test_parameter IN OUT VARCHAR2) IS
    BEGIN
      NULL;
    EXCEPTION
        WHEN OTHERS THEN NULL;
    END test_job;
END pkg_test;
/

Certainly seems like that exception handler allows the OTHERS handler to exit test_job without executing a RAISE or RAISE_APPLICATION_ERROR.

Well, PLW-06009, what do you have to say for yourself?

DID HE FIND A BUG?

No. The problem is that his code was just too trivial. The procedure does nothing - literally. It simply executes the NULL; statement.

Well, news flash: the PL/SQL compiler is smart enough to (a) figure that out, (b) conclude that the procedure couldn't possibly raise an exception, (c) ignore the exception entirely and therefore (d) not raise a warning (which would have then been converted into an error).

Good compiler!

Smart compiler!

(yes, that's right, there is a puppy in my household)

It's really easy to see that this is the cse.

Replace the NULL; statement with, say, a call to DBMS_OUTPUT.PUT_LINE, and then the procedure will not compile (it's still fairly trivial, but it does something that the compiler cannot safely ignore). You can see this from the SQLcl session below:

SQL> ALTER SESSION SET plsql_warnings = 'Error:6009';

Session altered.
 
SQL> CREATE OR REPLACE PACKAGE pkg_test
  2  AS
  3     PROCEDURE test_job (p_test_parameter IN OUT VARCHAR2);
  4  END pkg_test;
  5  /

Package PKG_TEST compiled

SQL> SHOW ERRORS
No errors.
SQL> CREATE OR REPLACE PACKAGE BODY pkg_test
  2  AS
  3     PROCEDURE test_job (p_test_parameter IN OUT VARCHAR2)
  4     IS
  5     BEGIN
  6        DBMS_OUTPUT.put_line ('abc');
  7     EXCEPTION
  8        WHEN OTHERS
  9        THEN
 10           NULL;
 11     END test_job;
 12  END pkg_test;
 13  /

Package Body PKG_TEST compiled

Errors: check compiler log

SQL> SHOW ERRORS

Errors for PACKAGE BODY QDB_PROD.PKG_TEST:

LINE/COL ERROR
-------- -----------------------------------------------------------------
8/12     PLS-06009: procedure "TEST_JOB" OTHERS handler does not end in RAISE or
         RAISE_APPLICATION_ERROR

So please keep this in mind when you are testing any sort of functionality in PL/SQL, particularly warnings and also performance optimizations. You've got to give the compiler a program that does something if you want to avoid confusion and unintended consequences.