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.

23 January 2012

Q4 2011 Championship Playoff on 25 January

The 7th quarterly championship playoff of the PL/SQL Challenge will take place on Wednesday, 25 January.

Forty-two players qualified to participate. In this playoff, players will have 30 minutes in which to take five quizzes. Scoring and ranking for the playoff follows the same formula used for the daily quiz.

While everyone cannot win, I wish all players the best as they demonstrate their expertise in the Oracle PL/SQL language!

Steven Feuerstein