Skip to main content

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.

Comments

  1. Hello Steven, All,

    Well ... if so, then what about these ?

    ALTER SESSION SET plsql_warnings = 'Error:6009';

    CREATE OR REPLACE PROCEDURE my_proc1 (p_out IN OUT VARCHAR2)
    AS
    c_do_stuff CONSTANT BOOLEAN := FALSE ;
    BEGIN
    IF c_do_stuff
    THEN
    p_out := p_out;
    END IF;
    EXCEPTION
    WHEN OTHERS
    THEN
    NULL;
    END;
    /

    Procedure created.


    CREATE OR REPLACE PROCEDURE my_proc2 (p_out IN OUT VARCHAR2)
    AS
    c_do_stuff CONSTANT BOOLEAN := TRUE ;
    BEGIN
    IF c_do_stuff
    THEN
    p_out := p_out;
    END IF;
    EXCEPTION
    WHEN OTHERS
    THEN
    NULL;
    END;
    /

    Procedure created.


    CREATE OR REPLACE PROCEDURE my_proc3 (p_out IN OUT VARCHAR2)
    AS
    BEGIN
    p_out := p_out;
    EXCEPTION
    WHEN OTHERS
    THEN
    NULL;
    END;
    /

    Errors: PROCEDURE MY_PROC3
    Line: 6 PLS-06009: procedure "MY_PROC3" OTHERS handler does not end in RAISE or RAISE_APPLICATION_ERROR


    The moral ?

    Better be always honest than sometimes overly smart ...

    It holds for people, as well as for compilers :)

    Cheers & Best Regards,
    Iudith



    ReplyDelete
  2. Hello Steven,

    My apologies for contacting you this way. it's the only way I could come up with, as I've left feedback on your blog several times in the past. I'll have to cut this in parts.

    I currently work in a new position (part of the Belgian pension fund) where the database platform is Oracle, but most new development is done in C# .NET. There is however a big legacy of PL/SQL code. For example, the whole pension calculation engine is written in PL/SQL. while I don't consider myself a guru or expert, I've 19 years of Oracle experience and I've always been a big advocate of SQL and PL/SQL. While it's always easy to judge afterwards, I've a lot of remarks about the quality of this PL/SQL legacy code and I believe there's quite some room for improvements. This being said, the code has done it's job for about 15 years. But the way it's been written make it not modular at all, hard to test, inaccessible for newcomers and hard to maintain. This of course is not the fault of PL/SQL as a language, but rather of past developers.

    The current team of architects (of which I'm kind of part of for my database input) has a very low opinion of PL/SQL and in my humble opinion, has limited knowledge of the language and even Oracle as a whole. Their decree is that no new code should be written in PL/SQL and everything should be written in C#. They are also big advocates of the service architecture. Nothing wrong with that, but I'm not convinced that it's a good idea to split the existing database schema of roughly 600 tabes (not my idea of a good schema, but that's what it is) into separate domains and replace existing foreign keys between tables with services between these domains. I've had quite a couple of heated debates about this in the short time I've been here. I've nothing against C# as such (my personal knowledge and experience are more Java based), but I'm still convinced that PL/SQL can play a useful role and is a better option for particular cases when using an Oracle database. If it were up to me, all data related logic would be in PL/SQL and database access would be only through stored procedures for writing action and views for reading actions. But there's no way I could sell that idea in the current climate and considering the fact we've very few experienced Oracle developers (most of them can't even write a decent SQL query statement), it would probably not be the best option in our case either. You could say that half the teams don't use any PL/SQL at all anymore (and most SQL is generated through entity relation mapping frameworks 'sigh'), while the other half still uses quite some PL/SQL.

    ReplyDelete
  3. We've agreed that we'll have one, last final discussion about this topic and after this, rules and guidelines for the development teams will be set up. In this context, I'm trying to collect as much useful information as possible (Bryn Llewellyn's recent whitepaper is a great source). I would like to make up a decent argumentation as to when and where I would still recommend using PL/SQL over C#.

    I'm thinking about:

    - Performance reasons, especially for batch jobs.
    - Obfuscation of complex queries.
    - Database management.
    - Security reasons, where I want to limit direct access to the tables.
    - Refactoring the huge, existing legacy code.
    ...

    This last part is quite important, as everybody has agreed that we won't rewrite the PL/SQL calculation engine (at least not in the near future), as it would simply take too much time and cost way too much money. But as said, this code has quite some issues and could seriously benefit from some refactoring.

    As you're the "absolute PL/SQL authority", I just wanted to ask if you could spare me a bit of your time and give me your opinion about this. What makes PL/SQL still relevant in your eyes and in which cases do you still recommend using it? I would really appreciate your input on this.

    Kind regards,

    Erwin Knop
    erwin.knop@telenet.be
    Erwin.Knop@rsvz-inasti.fgov.be

    ReplyDelete
    Replies
    1. I am very sorry to read of this, Erwin. It seems to me like the other architects have some legitimate concerns and ideas, but then are using this as an opportunity to "clean house" in a very dangerous way. I mean, seriously:

      "Nothing wrong with that, but I'm not convinced that it's a good idea to split the existing database schema of roughly 600 tabes (not my idea of a good schema, but that's what it is) into separate domains and replace existing foreign keys between tables with services between these domains."

      Get rid of database-level constraints and enforce consistency in distributed C# services? This seems like a recipe for disaster.

      In terms of resources, you are taking advantage of Bryn's Why Use PL/SQL? and hopefully other materials on his PL/SQL-EBR blog.

      Toon Koppelaars has also produced some powerful analysis re: performance.

      I will see about getting a set of links to resources for you.

      Delete
    2. Erwin, also be sure to check out this video, it's packed with useful data for making convincing arguments: https://www.youtube.com/watch?v=8jiJDflpw4Y

      Delete
    3. Thanks Steven,

      I really appreciate your input. I've used Bryn's whitepaper as the main source for writing my argumentation presentation (in combination with my personal experiences). Even after re-reading it for the upth time I was impressed by the solid thinking and argumentation behind it. I'll also take a look at the other sources you mentioned. I'm under no illusion and doubt I'll be able to change their minds, but I hope they will at least take some points into consideration for specific cases.

      Delete

Post a Comment

Popular posts from this blog

Quick Guide to User-Defined Types in Oracle PL/SQL

A Twitter follower recently asked for more information on user-defined types in the PL/SQL language, and I figured the best way to answer is to offer up this blog post. PL/SQL is a strongly-typed language . Before you can work with a variable or constant, it must be declared with a type (yes, PL/SQL also supports lots of implicit conversions from one type to another, but still, everything must be declared with a type). PL/SQL offers a wide array of pre-defined data types , both in the language natively (such as VARCHAR2, PLS_INTEGER, BOOLEAN, etc.) and in a variety of supplied packages (e.g., the NUMBER_TABLE collection type in the DBMS_SQL package). Data types in PL/SQL can be scalars, such as strings and numbers, or composite (consisting of one or more scalars), such as record types, collection types and object types. You can't really declare your own "user-defined" scalars, though you can define subtypes  from those scalars, which can be very helpful from the p

The differences between deterministic and result cache features

 EVERY once in a while, a developer gets in touch with a question like this: I am confused about the exact difference between deterministic and result_cache. Do they have different application use cases? I have used deterministic feature in many functions which retrieve data from some lookup tables. Is it essential to replace these 'deterministic' key words with 'result_cache'?  So I thought I'd write a post about the differences between these two features. But first, let's make sure we all understand what it means for a function to be  deterministic. From Wikipedia : In computer science, a deterministic algorithm is an algorithm which, given a particular input, will always produce the same output, with the underlying machine always passing through the same sequence of states.  Another way of putting this is that a deterministic subprogram (procedure or function) has no side-effects. If you pass a certain set of arguments for the parameters, you will always get

My two favorite APEX 5 features: Regional Display Selector and Cards

We (the over-sized development team for the PL/SQL Challenge - myself and my son, Eli) have been busy creating a new website on top of the PLCH platform (tables and packages): The Oracle Dev Gym! In a few short months (and just a part time involvement by yours truly), we have leveraged Oracle Application Express 5 to create what I think is an elegant, easy-to-use site that our users will absolutely love.  We plan to initially make the Dev Gym available only for current users of PL/SQL Challenge, so we can get feedback from our loyal user base. We will make the necessary adjustments and then offer it for general availability later this year. Anyway, more on that as the date approaches (the date being June 27, the APEX Open Mic Night at Kscope16 , where I will present it to a packed room of APEX experts). What I want to talk about today are two features of APEX that are making me so happy these days: Regional Display Selector and Cards. Regional Display Sel