Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Don't Test PL/SQL Features With Trivial Code

DZone's Guide to

Don't Test PL/SQL Features With Trivial Code

Please keep this in mind when you are testing any sort of functionality in PL/SQL, particularly warnings and also performance optimizations.

· Database Zone ·
Free Resource

Download the Altoros NoSQL Performance Benchmark 2018. Compare top NoSQL solutions – Couchbase Server v5.5, MongoDB v3.6, and DataStax Enterprise v6 (Cassandra).

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

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.

Download the whitepaper, Moving From Relational to NoSQL: How to Get Started. We’ll take you step by step through your first NoSQL project.

Topics:
database ,plsql ,database performance ,exception handler

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}