27 January 2012

Compiling Incomplete Program Units (9633)

The 25 January quiz tested your knowledge of the minimal amount of code needed to "create" a PL/SQL procedure in the data dictionary, even if the resulting program unit is invalid.

The choices offered were:

CREATE PROCEDURE plch_test;
CREATE PROCEDURE plch_test AS;
CREATE PROCEDURE plch_test NULL;
CREATE PROCEDURE plch_test IS BEGIN NULL; END;

The first was scored as incorrect and the last three as correct.

Several players demonstrated ways to execute these statements in either Toad or SQL Developer that either (a) caused the first statement to create an object (invalid procedure) or (b) caused the third statement to not create an object in the database.

The author of this quiz, Jeff Kemp, did some research and found the following:

SQL*Plus: no problem - the first statement does not create any object, the latter 3 statements do create an object.

TOAD - I get varying results for option 3, depending on how I invoke the statement in the SQL editor.

Quiz Option 1: CREATE PROCEDURE PLCH_TEST;

1. Highlight statement, press F5
  Result: "ORA-00911: invalid character" - no object created

2. Highlight statement, press F9
  Result: "[Error] Syntax check (5: 27): Found:  ';' Expecting: AS  IS   -or-   ......" - no object created

3. Highlight statement, press Ctrl+Enter
  Result: "[Error] Syntax check (5: 27): Found:  ';' Expecting: AS  IS   -or-   ......" - no object created

So I haven't been able to create an object using the first quiz option at all. By the way, each of the above tests were repeated after highlighting just the statement by itself, as well as highlighting the statement plus the following slash (/).

Quiz Option 3: CREATE PROCEDURE PLCH_TEST NULL;

1. Highlight statement, press F5
  Result: object created with compilation error

2. Highlight statement, press F9
  Result: "[Error] Syntax check (5: 27): Found:  ';' Expecting: AS  IS   -or-   ..." - no object created

3. Highlight statement, press Ctrl+Enter
  Result: "[Error] Syntax check (5: 27): Found:  ';' Expecting: AS  IS   -or-   ..." - no object created

Looks like if you use the F5 option, the third CREATE PROCEDURE quiz option does work in Toad.

I, on the other hand, was able to get the first choice to create an object by pressing the green arrow button in the Editor menu in Toad. Go figure.
The assumptions for the PL/SQL quizzes state that you can use your choice of IDE (SQL*Plus, Toad, SQL Developer, etc.). For this quiz, that turned out to be a very tricky assumption, because the IDEs offer multiple ways to execute statements that handle the terminating character of the statements differently.
 
For some, the semi-colon is treated as part of the code. In others, it is treated as the terminator of the statement, to trigger execution. 

As a result, I will be giving everyone credit for the 1st and 3rd choices. In addition, I have changed the question to specify that these statements execute in SQL*Plus and I have explicitly included the "/" to execute the buffer.

5 comments:

  1. Hello All,

    I could hardly believe that a quiz like this one will also generate a blog thread ...
    but here it is :)

    In fact, for the sake of consistency, I think that ANY of the development tools should have been able to create the object as soon as the object type and name are correctly specified, like in the first option.

    This would have been similar to the statement that creates an incomplete object type,
    by specifying only

    CREATE TYPE object_type_name;

    and defers the completion of the object definition.

    We can go even further, and imagine that a procedure without a "body" could have been interpreted as having a default body composed of a single "NULL;" statement, and thus to even compile it as a VALID object ...
    This is maybe already too much, but, definitely, I don't see a reason for which
    the first choice to be "discriminated" and being considered "less worth" of creating an object than are the other three ones ...

    It is interesting however to know that the different IDE tools have different behaviors ...
    Those who are addicted to SQL*PLUS (like me) probably did not even think of such a possibility.

    Thanks a lot & Best Regards,
    Iudith

    ReplyDelete
  2. I was surprised at the results. It had never occurred to me to try to create procedure with only a name, so I tried it. I happened to have Toad open and, wonder of wonders, it worked. The same behavior is in Oracle's own SQL Developer too.

    To remove the tool from the equation, try this anonymous block.

    DECLARE
    object_does_not_exist EXCEPTION;
    success_with_compile_error EXCEPTION;
    PRAGMA EXCEPTION_INIT(object_does_not_exist, -4043);
    PRAGMA EXCEPTION_INIT(success_with_compile_error, -24344);

    v_type user_objects.object_type%TYPE;
    BEGIN
    BEGIN
    EXECUTE IMMEDIATE 'DROP PROCEDURE plch_test';
    EXCEPTION
    WHEN object_does_not_exist
    THEN
    DBMS_OUTPUT.put_line('oops! tried to drop an object that did not exist');
    END;

    BEGIN
    EXECUTE IMMEDIATE 'CREATE PROCEDURE plch_test';
    EXCEPTION
    WHEN success_with_compile_error
    THEN
    DBMS_OUTPUT.put_line('Created ok but is invalid');
    END;

    SELECT object_type
    INTO v_type
    FROM user_objects
    WHERE object_name = 'PLCH_TEST';

    DBMS_OUTPUT.put_line(v_type);
    END;

    ReplyDelete
  3. I think the assumptions should state that you should use only SQL Plus and the standard command terminator (since I guess you can change the terminator in SQL Plus by SET command).

    I hardly use GUI tools as I am scared of losing my command line skills. And also I don't trust the tools 100%.

    In my opinion for critical tasks like production DB administration you should only use Oracle supplied tools or command line.

    ReplyDelete
  4. This quiz tested one’s knowledge of the internal quirks of the PL/SQL compiler and various methods for submitting PL/SQL for compilation. Although such details may be interesting, they are not a test of one’s PL/SQL knowledge. Whether syntactically flawed PL/SQL results in an invalid object or none at all is of little importance. The only guarantee is that compiling invalid PL/SQL will not result in an executable object.

    The choice of tool for submitting PL/SQL can have a significant impact on the processing of submitted PL/SQL, as can the various versions of the Oracle client libraries. A few years ago I was writing some code that utilized PL/SQL features that were introduced with the then newly released version of Oracle RDBMS. Despite using the most recent client libraries, I was unable to get the code to compile via TOAD. Submitting the file via SQL*Plus was successful. After some experimentation it was concluded that the problem was due to TOAD’s not submitting the code to the database when Toad determined that the syntax was invalid. My experience with recent versions of TOAD indicates that this approach has been abandoned by Quest.

    For most quizzes the use of SQL*Plus, SQL Developer, TOAD, or some other tool will have no significant impact on the results; however, the assumptions should specify that the most recent version (at the time of the quiz) of SQL*Plus or SQL Developer will be the standard for verification. These tools support multiple platforms and are readily available to anyone participating in the PL/SQL Challenge.

    ReplyDelete
  5. You can blame me if you didn't like this quiz :)

    jhall62: "This quiz tested one’s knowledge of the internal quirks of the PL/SQL compiler and various methods for submitting PL/SQL for compilation."

    Maybe, but I'm not so sure about that.

    The quiz was not about different *methods* of submitting PL/SQL for compilation (it was my mistake that the assumptions did not explicitly restrict it to SQL*Plus). I never intended it to have anything to do with whether TOAD, SQL Developer, or any other tool was used.

    The quiz's purpose was to test one's knowledge of whether an object would be created depending on different *syntax* used, even if the syntax was invalid. Perhaps the case of "CREATE PROCEDURE x;" (which results in no object) is a bug in the compiler - in which case I might concede that this quiz is about internals; but I'm not sure I'd characterize this as a bug, just an "undocumented behaviour" :)

    I did mention in private correspondence with Steven that I considered this quiz to be a trivia question. I'm glad he included it anyway, because I enjoy trivia questions as well as the good educational ones - just so long as there aren't too many of them.

    ReplyDelete