No Error Log Table, So No Choices Correct? (7990)

The 13 October quiz tested your knowledge of the DBMS_ERRLOG package and what happens when you try to create an error logging table for a table that has unsupported column types, such as CLOB.

Several players wrote with objections along this line:

I disagree with your answer to this quiz. You say that ORA-00942... propagates unhandled from the block. While this is true, I still selected 'none of the answers are true' because before this error shows, you get the following error when you attempt to execute the create_error_log statement: 'ORA-20069: unsupported column type(s) found: CLOB_VALUE....' so in my opinion, none of the answers are correct.

The question stated "I execute the following statements:" and indeed the execution of this block:

BEGIN
   DBMS_ERRLOG.create_error_log (dml_table_name => 'PLCH_EMPLOYEES');
END;
/

will result in ORA-20069 being raised. But we never stated that each of these statements ran without error, only that we ran them.

Then, because the error logging table was not created, the attempt to use LOG ERRORS with the DML statement causes ORA-00942 (table or view does not exist) to be raised.

My main objective with this quiz was to make you aware that you could get a very puzzling error (table or view does not exist) from a DML statement against a table that clearly does exist. It is not in any way obvious that the error has to do with the fact that the underlying, unnamed error logging table does not exist.

But if you agree that "this is true" - that (to repeat the choice we scored as correct):

The following exception propagates unhandled from the block:

ORA-00942: table or view does not exist

Then I do not see how you can also argue that no choices are correct.

Your thoughts?

Cheers, Steven Feuerstein

Chinese Translations of PL/SQL Challenge Quizzes

James Su, a PL/SQL Challenge player, recently asked for permission to translate quizzes and post them on a site for Oracle developers working in the Chinese language.

I was, of course, delighted to give him this permission.

You can now check out his work at:

http://www.itpub.net/thread-1499223-1-1.html 

If anyone else is interested in translating quizzes, you have my permission in advance - as long as you agree to include links to the PL/SQL Challenge website, so that we can get other people playing the quizzes in "real time."

Typo in Question Text Requires Score Adjustment (7988)

For the first 11 or so hours of 11 October 2011, we asked players to pick a block of code that would display this string:

1*October*2011*12:01:01

As a number of players noted, none of the choices would do the trick - and they wondered if we'd made a mistake. Yes, indeed, that was a typo. So at 6:15 AM Chicago time, Steven woke up, checked his email and was immediately at all the bug reports.

Then he changed the question text to:

1*October*2011*12*01*01

so that the several hundred other Oracle technologists who would be playing the quiz on the 11th would have a more interesting experience.

As a result of these actions, we will issue a correction to the 189 players who chose "Not correct" for the fourth choice (which should have been correct).

It is, of course, frustrating to everyone when we make mistakes like this, and I (yes, this is me, Steven, talking earlier in the third person) apologize to all. But when I am done apologizing, I like to think about how mistakes like this can happen.

You see, I was especially frustrated this morning because at about 03:00 on the 11th (10 PM Chicago time), I received an email from Jeffrey Kemp, one of the most diligent and expert of our players who also happens to live in Perth and so he plays very early in the day (UTC-timewise). He reported this problem. So I took a look. I looked at the question text. I ran the code for the "correct choice"...

And it looked just fine to me! And so another 8 hours passed, and hundreds of other answers submitted, before I took a closer look and recognized/accepted my error.

How could this be? Because we humans have a tendency to see what we want or expect to see, rather than what is really there. This "quick and dirty" approach to life can be helpful at times, but when it comes to analyzing and debugging code, it is a downright nasty tendency.

Professional proofreaders scan text backwards so that they will not be able to read the text and thereby make all sorts of assumptions, and skip over problems in the text. We programmers can't read our code backwards, so we have to make an extra effort to force ourselves to really look at, really read, what we've written, and make sure it makes sense.

That's what I am going to do from now on. Promise.

Empty String as Index Value in Collection (7987)

The 10 October 2011 quiz tested your knowledge of the effect of a NULL value being passed to the EXISTS method. Iudith Mentzel did some further analysis and offers up these insights:

First, as probably expected, we cannot use NULL as an array index value, neither for an associative array indexed by PLS_INTEGER nor for one indexed by a VARCHAR2, they both raise VALUE_ERROR:

DECLARE
    my_list   DBMS_SQL.number_table;
BEGIN
    my_list(NULL) := 100;

    IF my_list.EXISTS(NULL) THEN
       DBMS_OUTPUT.PUT_LINE('NULL index exists !');
    ELSE
       DBMS_OUTPUT.PUT_LINE('NULL index does not exist !');
    END IF;
END;
/
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: NULL index table key value
ORA-06512: at line 4
 
DECLARE
    TYPE array_t IS TABLE OF NUMBER INDEX BY VARCHAR2(5);
    my_list   array_t;
BEGIN
    my_list(NULL) := 100;

    IF my_list.EXISTS(NULL) THEN
       DBMS_OUTPUT.PUT_LINE('NULL index exists !');
    ELSE
       DBMS_OUTPUT.PUT_LINE('NULL index does not exist !');
    END IF;
END;
/
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: NULL index table key value
ORA-06512: at line 5

However, for a VARCHAR2 index we can use an empty string ( '' ) as an index without error, and it is NOT the same as a NULL index:
 
DECLARE
    TYPE array_t IS TABLE OF NUMBER INDEX BY VARCHAR2(5);
    my_list   array_t;
BEGIN
    my_list('') := 100;

    IF my_list.EXISTS(NULL) THEN
       DBMS_OUTPUT.PUT_LINE('NULL index exists !');
    ELSE
       DBMS_OUTPUT.PUT_LINE('NULL index does not exist !');
    END IF;

    IF my_list.EXISTS('') THEN
       DBMS_OUTPUT.PUT_LINE(' '''' index exists !');
    ELSE
       DBMS_OUTPUT.PUT_LINE(' '''' index does not exist !');
    END IF;
END;
/

NULL index does not exist !
'' index exists !

This is in spite of the fact that a VARCHAR2 variable having an empty string assigned to it is considered as NULL (which, as we know, is NOT the case for a CHAR variable):

DECLARE
     my_var   VARCHAR2(5) := '' ;
BEGIN
     IF my_var IS NULL THEN
        DBMS_OUTPUT.PUT_LINE('my_var IS NULL');
     ELSE
        DBMS_OUTPUT.PUT_LINE('my_var IS NOT NULL');
     END IF;
END;
/
my_var IS NULL

Strange NULL oddities ... 

Without this quiz, no one has probably ever thought of trying to use a NULL or a NULL variable as an array index, just to see what happens ...it is entirely the merit of PL/SQL Challenge to make us dig that deeply :-) 

Start and End Dates of the Oracle Magazine Quizzes

We've received some feedback players expressing confusion over the start and end dates for the Oracle Magazine quizzes and, related to that, when you can see the answers to the quizzes.

Each issue of Oracle Magazine is available in both online and print edition. Generally, the online edition is available one month prior to the print version, or at least prior to the published months for the issue.

In other words, the Sept/Oct 2011 edition of Oracle Magazine can be seen online starting August 1.

So each Oracle Magazine quiz starts one month earlier than the published starting month, and it overlaps (is still open) the quiz for the next edition. This means that:

1. Two Oracle Magazine quizzes are open at any given point in time.

2. You can't see the answers for the quiz for the previous edition even though the quiz for the next/current edition is available for answering.

Cheers,
Steven Feuerstein