So which error is raised in 24 December quiz? (1805)

The 24 December quiz tested your knowledge of what happens when a CASE statement does not contain an ELSE clause and at runtime, none of the WHEN clauses are executed. The answer is that Oracle raises the "ORA-06592: CASE not found while executing CASE statement" error.

Several players wrote, however, to note that the local variable, l_text, is declared as VARCHAR2(20). So this assignment:

the_text := 'Santa is coming (for some, sort of, maybe)';

will raise a VALUE_ERROR exception. In other words, they chose the correct answer, but for the wrong reasons.

It is true that if statement assignment was executed, VALUE_ERROR would be raised and the text "Today is the 24th" would be displayed on the screen.

But that statement is never executed because of the lack of an ELSE clause.

I did not intend to introduce this issue into the quiz; the original quiz submitted by Michael used just "Santa is coming". But I "softened" the text - after all, many do not celebrate, do not believe in Santa, etc. - and neglected to increase the size of the string.

I will correct this in the question text. There will be no re-scoring of quiz answers.

Merry Christmas to those who celebrate,
Steven

Is a CLOB a string? A question raised about the 22 December quiz (1803)

The 22 December quiz tested your knowledge of the capabilities of both EXECUTE IMMEDIATE and DBMS_SQL to parse very long strings. We scored the following statement as incorrect:

"You cannot execute a string of more than 32,767 bytes as a dynamic SQL statement."

Oracle offers various mechanisms, especially in Oracle11g to bypass this limitation (the maximum size, that is, of a VARCHAR2 variable or literal).

One player emailed the following concern:

One of the choices for today's quiz looks somewhat ambiguous, namely the choice that says: "You cannot execute a string of more than 32,767 bytes as a dynamic SQL statement". If we take this literally as it is worded, then we could object that there is no such thing at all as a "string of more than 32,767 bytes", because this is the maximum length allowed to a VARCHAR2 string. If so, then this choice is supposed to be marked as correct. On the other hand, if string means "anything that is character data", including a CLOB, then this choice (though apparently in need to be marked as incorrect) falls over 2 other choices, namely: a) the choice that says "You can use a subprogram of the DBMS_SQL package to parse a SQL statement whose length exceeds 32767 bytes ( by the way, the wording of this choice ellegantly avoids speaking of a "string whose length exceeds 32767, but speaks of a STATEMENT whose lengths exceeds 32767, thus avoiding the above problem). and b) the choice that says "In Oracle11g you can pass a CLOB to EXECUTE IMMEDIATE. These 2 choices in fact do cover in entirety the 2 cases of using a statement contents as a CLOB. I think that the wording of the choice that says "You can use a subprogram of the DBMS_SQL package to parse a SQL statement whose length exceeds 32767 bytes" is excellent, covering (even without specifying it explicitly) ALL the cases by which a statemnent longer than 32767 can be made "to fit into" the requirements of DBMS_SQL, be it by using a CLOB or by "breaking" the statement's contents into elements of a DBMS_SQL.VARCHAR2A array. So, in my opinion, the problematic choice recommends itself to be rescored due to the ambiguous wording.

The SQL Language Reference says the following about CLOBs: "The CLOB data type stores single-byte and multibyte character data." CLOBs are, therefore, character data, which is another general term for string. VARCHAR2s are also character data, with a maximum length of 32767. But I believe that it is correct to interpret string as a general category of data including CHAR, VARCHAR2, NCHAR, NVARCHAR, CLOB and NCLOB (any others?). Others also feel this way; see:  http://www.orafaq.com/forum/t/98334/2/.

You are correct that two other choices covered the correct descriptions of ways to parse very long strings. I do not see, however, what bearing that fact has on the other choice, which is incorrect.

So I do not see the need to rescore. What do you think?