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