Answer to 5 November Quiz is Wrong (1621)

The 5 November quiz tested your knowledge of the way that the value of the SQL%ROWCOUNT attribute is set. Vitaliy wrote to me that while the quiz was scored correctly, the explanation of the results are wrong: 1. Until a SQL data manipulation statement is executed, SQL%ROWCOUNT yields NULL. Not zero. 2. SQL%ROWCOUNT=0 in your code because the COMMIT changes the SQL% attributes (reset rowcount to 0). (JFYI, in 7.3 commit does not reset SQL%ROWCOUNT to 0 and when upgrading some people had trouble with it). Vitaliy then points us to the Oracle documentation: "The value of the SQL%ROWCOUNT attribute refers to the most recently executed SQL statement from PL/SQL....The SQL%ROWCOUNT attribute is not related to the state of a transaction. ...Also, when an autonomous transaction is exited, SQL%ROWCOUNT is not restore to the original value in the parent transaction." I will change the text of the answer so that it is accurate. My apologies for this - but of course happy as always to be corrected! SF

The quote character (q) and the 4 November quiz (1604)

This quiz tested your knowledge of the new-to-10g feature of using the q character so that instead of doubling each single quote inside a string literal, you can specify your own delimiter character for the literal, and then use single quotes inside the string, thereby making it easier to read and manage dynamically constructed strings. I received several emails with highly varied responses to this topic, some indicating what I can only consider are bugs in this feature or in the way they were used when players tested the feature. I have had no problems with using the q character on both Oracle10g and Oracle11g databases. 1. "I loved today's question, I was not aware of the q function...and I could immediately use it today, so another victory for the Challenge!" 2. "Something strange happens related to this quiz, regardless of the choices presented. It looks like, in general, the alternative quoting mechanism does not work at all if the literal string contains a SINGLE QUOTE character as the first character of the literal value. This restriction IS NOT documented in the Oracle SQL Language reference, however it gives the following error: SQL>select q'*'Hello,' said the man, who didn't like goodbyes.*' 2 from dual; ERROR: ORA-01756: quoted string not properly terminated though, as per the documentation, this was supposed to work. Removing the first quote character and leaving all the alternative delimiter stuff in place makes things working. The case is the same for both SQL and PL/SQL statements. The documentation only says that: "...if the quote_delimiter appears in the text literal itself, ensure that it is not immediately followed by a single quotation mark." which IS NOT our case. This practically renders ALL the choices to this quiz as incorrect, except for the one that uses the classical quote delimiter, though I am sure that this WAS NOT your intention, you probably intended to check the knowledge about the "alternative quoting" feature, and not to make all of us stumble upon this documentation bug ... I dare say that: 1. Most of the players were not aware of this restriction regarding the first character in the literal. 2. It could be interesting to know what percent of people are using this mechanism at all in their daily work ... I am sure that many other players will comment on this quiz." 3. From Christian Rokitta: "I just published a blogpost on today's quiz subject" 4. "Thanks for bringing this topic up. I did not know that Oracle had introduced this ability. (Thus I got a very poor score in yesterday's quiz) I tried it out immediately - but your examples did not work on my 11g database (I know, the quiz assumes a 10g database, but features introduced in 10g should also be available an work in 11g also). Playing a bit around I found out that the user-defined delimiters works when there are a even number of quote signs in the string literal, but not when the number of quote signs is uneven, as in your examples. I wonder, is this a bug in 11g?" 5. "This is a new one for me, the Q mechanism. Had to quickly search for the documentation and made one mistake, the () I oversighted. But is the sentence correctly English? The comma should not be there?" Regarding #5: Hmmm, you know, I think you are right. I am used to putting a comma before and after the speaker of a quotation, but perhaps in this case the "who" should flow directly after the "man". I will check with my wife, who has a degree in English literature. I am puzzled by the reports of this functionality not working. Again, I did not experience any problems. Perhaps you could use the verification code on the Past Quizzes page and see if, indeed, my precise code in the choices are causing you errors. Let us all know what you find out.

On Winners and Adjustments for the end of October

You will find on the PL/SQL Challenge a list of the most recent winners. I ran into an interesting and new circumstance for October that I wanted to highlight for you.

At the end of each month I account a winner of the "most correct" raffle. You are eligible to participate in this raffle if your correctness over the month was at least as high as the #1 ranked player -regardless of the amount of time it took you to answer the quizzes.

When I ran my script to pick this "most correct" winner for October, my code raised a NO_DATA_FOUND exception. After a quick debug session, I discovered the reason:

No other players matched the 100% correctness ranking of Eurico Matos in October 2010!

Achieving 100% correctness for an entire month is quite an achievement in general, and even more so when no other players were able to do so. Congratulations, Eurico!

I ran the adjustment algorithm for very fast answer times this weekend as well, so some of you might notice a sudden downward shift in your rankings. Remember: take your time, read carefully and thoroughly.

Cheers,
Steven Feuerstein

A better alternative to NVL2 (29 October quiz) (1565)

The 29 October quiz tested your awareness of the NVL2 function (which works quite differently from NVL), and the fact that it is available only in SQL, not yet in the PL/SQL language. The quiz offered several options that served, in effect, as alternatives to NVL2, from within PL/SQL. Vitaliy wrote to suggest that I had missed a simpler and more elegant alternative: CASE. So just in case you would like to use it....

CREATE OR REPLACE FUNCTION NVL2 (val         IN VARCHAR2
                              , ifnotnull   IN VARCHAR2
                              , ifnull      IN VARCHAR2)
  RETURN VARCHAR2
IS
BEGIN
  RETURN CASE WHEN val IS NOT NULL THEN ifnotnull ELSE ifnull END;
END;
/

26 October quiz: objections to marking a choice as incorrect (1583)

We are about to kick off the OPP/APEXposed conference in Brussels, so I will post this note to get the discussion going, and going in a very lively manner. Then I will come back to it later. In this quiz on read-only transactions, I marked the following choice as incorrect: "The SET TRANSACTION READ ONLY statement can appear anywhere in your PL/SQL block, regardless of the location of SQL statements in that block." Here is one of the objections:

This answer should be marked "correct" : it says nothing about what will or won't happen when the code is *executed*. It only says "can appear anywhere in your PL/SQL block", which I take to mean "the PL/SQL block will compile without error regardless of where (in the executable section) the statement appears".

I will ask the others to post their concerns on this blog. Then I will offer my response later. Cheers, SF

Questions about SQL%ROWCOUNT and FORALL from 20 October Quiz (1523)

Whoops! I overlooked (or, to be more honest, was too busy to respond to) a couple of submissions by players regarding this quiz that tested your knowledge of using the INDICES OF clause with FORALL. Here are the questions/comments: * "When the update statement is not executed, sql%rowcount is not 0, but it is NULL. dbms_output does not write out 0, but a blank space." * "Can you please give more information on why SQL%ROWCOUNT is null returned as null when we use the forall construct with no elements referenced? I am wondering if there will ever a case where a null is returned because the update is never fired." In fact, it is true: if no SQL statement is executed, then SQL%ROWCOUNT returns NULL, as I verified by running the following block (see the Verification Code for this quiz on the Past Quizzes page for the create table statement for parts, and so on).

DECLARE
  TYPE part_aat IS TABLE OF parts.partnum%TYPE
                      INDEX BY PLS_INTEGER;

  l_parts   part_aat;
BEGIN
  FORALL l_index IN 1 .. 0
     UPDATE parts
        SET partname = UPPER (partname)
      WHERE partnum = l_parts (l_index);

  DBMS_OUTPUT.put_line ('Rows modified = ' || SQL%ROWCOUNT);
END;

Was I, then, wrong in scoring the following choice as correct (with the associated explanation)?

BEGIN
  update_parts (100000, 10000000);
END;

"And the output displayed is: Rows modified = 0. There are no elements defined in l_part_indices between 100000 and 1000000. As a result, none of the index values in l_parts will be used, no elements of the l_parts array will be bound into the DML statement, and no rows will be updated."

Hmmm. A very good question. SQL%ROWCOUNT definitely returns 1, but I cannot explain it to my satisfaction. It looks as if, with the INDICES OF and BETWEEN clauses, the FORALL gets "far enough" to be considered by the SQL engine to have run something, but having not actually changed any rows returns 0 for that attribute. Anyone out there have a better explanation? Cheers, SF

25 October Quiz Requires Correction (1561)

First, my apologies for the lateness of this correction. I flew from Chicago to Brussels last night for the OPP/APEXposed conference. Just catching up with all things PL/SQL Challenge now (13:30 Tuesday). So: this quiz tested your (our) knowledge of how the ASCII function handles strings with more than one character. Namely: it ignores all characters after the first and "does its thing" with just that first character. I therefore scored as correct the following choice: "The ASCII code of just the first character is returned." I even wrote some code to test it. My reviewers checked it over. We all liked it. But one player, Ryan, objected. He claimed the choice is not correct, because: "The quiz assumptions make it clear that you are NOT using ASCII, since they state that 'the database character set is an 8-bit character set.' ASCII is a 7-bit character set. It is sometimes encoded as 8-bit, but even then the character set itself is still 7-bit. You cannot guarantee you'll get the ASCII value out of the function when the database is using an 8-bit character set, as the first character of the supplied string could easily be a character which does not have an ASCII representation. If you added an assumption to the question that either that the supplied string either does not start with (almost gives away the correct answer) or does not contain any non-ASCII characters, then you could get away with the answer as written. Or you could just state that the database was using a 7-bit ASCII character set." Ah, Ryan, you are so right. And I find myself now saying: "If only I had checked the Oracle documentation first." Because it describes the ASCII function as follows: "ASCII returns the decimal representation in the database character set of the first character of char. char can be of datatype CHAR, VARCHAR2, NCHAR, or NVARCHAR2. The value returned is of datatype NUMBER. If your database character set is 7-bit ASCII, then this function returns an ASCII value. If your database character set is EBCDIC Code, then this function returns an EBCDIC value. There is no corresponding EBCDIC character function." In other words, if I had only phrased my choice as: "The decimal representation of just the first character is returned." Then everything would have been fine. That was careless of me (well, and I must admit, ignorant). I have now added another item to my checklist for reviewing quizzes: "Check the Oracle documentation. It may not be 100% correct, but it certainly is an important place to start." In addition to all that, one of my reviewers noted that our assumption states "the database character set is an 8-bit character set; the national character set is AL16UTF16." But that character set is not 8-bit, it is 16-bit. So we have a problem there as well. The 10.2 Character Set Support documentation states: "Oracle uses UTF-8 (8-bit) encoding by way of three database character sets, two for ASCII-based platforms (UTF8 and AL32UTF8) and one for EBCDIC platforms (UTFE). If you prefer to implement Unicode support incrementally, you can store Unicode data in either the UTF-16 or UTF-8 encoding form, in the national character set, for the SQL NCHAR datatypes (NCHAR, NVARCHAR2, and NCLOB)." If anyone's eyes are glazing over at all of this, I can sure sympathize! Character sets, Unicode, globalization, localization: these are well outside my comfort zone. Why can't we all just speak (and write) English? :-) I will take the following actions in light of this mistake: 1. Change the text of the choices to use "decimal representation" instead of "ASCII code". 2. Give everyone credit for a correct answer on that choice (and change your choice accordingly). 3. Rescore and recalculate rankings. 4. Change the assumptions so that the assumed character set is AL32UTF8. And as Ryan was the only player to report this issue, he wins his choice of an O'Reilly Media ebook. Thanks for the education, Ryan! Cheers, StevenSo: t