Watch out for impact of NLS_SORT and NLS_COMP settings on string comparisons (1645)

The 12 November quiz asked you to evaluate various implementations of ways to find matches on strings in a collection. Peter wrote with the following observation: "This is actually not a question about a quiz, nor an error/ambiguity report; just my intention to point out an environment-dependent behaviour in one of the quiz answers. It's about the 12 November's quiz and the answer choice using associative array copied from the original nested-table collection. Well, the thing is, the output of the given anonymous PL/SQL block is dependent on the setting of two parameters - NLS_COMP and NLS_SORT. Given the standard setting of NLS_COMP = BINARY, the block would yield the TRUE, TRUE, FALSE as requested in the quiz question. "However, if NLS_COMP is set to LINGUISTIC and NLS_SORT is set to e.g. BINARY_CI, the block would output TRUE, TRUE, TRUE, as the result of 'Steven' being case-insensitively equal to 'steven'. This behaviour is well described in Oracle DB documentation. I search the "Rules" about Oracle environment assumptions and found no mention about these specific settings. So I just would like to point it out; I don't really take this to be a quiz error. "The second (for me *the* more interesting thing about this quiz) is the behaviour of the MEMBER OF operator used in one of the other answer choices. I would think that this operator, as it's (I believe) merely comparing the array values against a single value, would be prone to produce different results depending on NLS_COMP/NLS_SORT parameters too. Yet, it is not. I cannot explain this, I merely point out again as an interesting fact. All my tests were done on a 11.2.0.1 version of Oracle EE DB. I don't have a 10g R2 to play with so cannot tell if the MEMBER OF thing is anyhow 11g specific." Many thanks, Peter! My sense is that our assumptions (default installation of Oracle) means that we do not have to add another assumption. But this is fascinating and useful information about which we should all be aware. Cheers, SF

Numbers are names, too! A mistake in the 11 November quiz (1644)

The 11 November quiz contained a mistake, due mostly to incomplete "specifications" in the question text. The question stated that I created a table like this:

CREATE TABLE plch_employees (
  employee_id INTEGER,
  last_name VARCHAR2(100)
)

and then ran a block at 2 AM each morning that worked with data from this table. I scored as correct the following choice: "Oracle will raise a NO_DATA_FOUND exception if all the employee last names have no more than 10 characters." and my explanation for this choice stated: "If I manage to populate the collection with rows from my table, then Oracle will certainly raise NO_DATA_FOUND when it tries to execute the numeric FOR loop. This is so because the index values are strings and not integers, so there is no possible way that the collection is sequentially filled from index value 1. As a result, this code will raise a NO_DATA_FOUND exception - unless the employees table is empty." Well....many, MANY players had a field day with this choice. They were especially entertained by my "no possible way" phrase. You see, I never stated any rules about an employee name. Consequently, the names in the table could all be integers (1, 2, 3, etc.) and in this case, the block would not raise NO_DATA_FOUND. And, even more sadly, this mistake affected TWO of the choices for the very same reason. The choice "I will see the employee IDs for every row in the employees table." could be true if all employee last names were integer values starting with "1" and proceeding sequentially to the number of employees in the table (1,2,3...). Silly me - and very sharp of so many players for noticing this gap in my specifications and therefore the possible behaviors of my code. I am, once again, impressed at the close attention paid by so many PL/SQL Challenge players to these quizzes. Assuming you pay the same amount of attention to your application code, I am certain those applications are of the highest quality! I will give everyone credit for both these choices, and change the question text so that it is clear that names contain characters. From all those who notified me of my mistake, Oleg Gorskin was selected randomly to win an O'Reilly Media ebook. Congrats, Oleg! Any other comments/objections to this quiz? Cheers, SF

Questions regarding NEW and OLD quiz of 10 November (1643)

The 10 November quiz asked: "Which of the following statements correctly describe how you can reference the new and old column values of the current row affected by the triggering statement of a row-level database trigger?" Several players wrote to us with the following concerns: 1. We scored the following as incorrect, because if you use a WHEN clause for your trigger, any references to NEW and OLD within this part of the trigger cannot have a ":" prefix. "A colon (:) must precede the OLD and NEW qualifiers wherever you reference the old and new column values in the code to define a row-level trigger." Several players objected to this, stating the "the code" to define a trigger only refers to the part of the trigger that starts with the DECLARE or BEGIN section of the trigger definition. I do not agree. I think that "the code to define a trigger" refers reasonably to all the statements that you write to define the trigger. Another person objected to this scoring as follows: "But in the WHEN clause you are not referencing "column values" as you write in the answer. You are just merely defining the keywords. So "wherever you reference the old and new column values" you do need to specify a colon. And therefore I feel that this answer is correct." How is it that I am not referencing column values when I write:

WHEN NEW.employee_id = OLD.employee_id 

This interpretation of "code" to not include parts of the CREATE TRIGGER statement and the statement that I reference only the "keywords" and not the values seem to me to be parsing words a bit too closely; some measure of reasonable interpretation is required and these objections both fail on that count. I do not feel that any re-scoring is required for these objections, though I may consider making some changes to the quiz text to the possibility of mis-interpretation in the future. 2. A player wrote: "There is a choice in the today quiz starting with "The trigger fired by the DELETE statement ..." - it does not state that the trigger is a row-level one, so I did not mark it as valid. Is it not mentioned intentionally? BTW, the similar statement regarding triggers fired by INSERT explicitly states the the trigger is row-level one." To which I reply: If the choice that you point out was a "stand alone" statement, then your concern is valid and you would be right to mark it as incorrect. It is, however, a choice for the following question: "Which of the following statements correctly describe how you can reference the new and old column values of the current row affected by the triggering statement of a row-level database trigger? " so clearly the context is "row-level" and I do not believe that any change is needed to the scoring. I will, however, add "row-level" to that choice so that in the future others do not experience the same problem. Cheers, Steven Feuerstein

Ambgiuous reference to "storage clause" in 8 November quiz (1641)

In the November 8 quiz, we scored the following statement as correct: "When you use a nested table type as a column datatype, you must also include a storage clause for that column." P.H. wrote to say that he disagreed, as he could create a nested table as follows:

create table t_test_nt_3
 (
          col1    arr_numbers,
          col2    arr_numbers
 )
 nested table col1 store as t_test_nt_3$col1
    storage (initial 1m next 1m),
 nested table col2 store as t_test_nt_3$col2;

Well, yes, you can do that. But what I meant by "storage clause" was the entire STORE AS...[STORAGE ...] clause. I can certainly see, however, why this term was not clear enough, and left the quiz (and me) vulnerable to a charge of ambiguity- unless Oracle itself was very clear on this topic in its documentation. Unfortunately, it is not. I found two references to "storage clause" as the entire STORE AS ... STORAGE part of the column declaration: http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28371/adobjcol.htm#sthref461 http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28371/adobjdes.htm#sthref834 These pages support my use of the term "storage clause" and my scoring of the choice as correct. This link, http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28371/adobjcol.htm#sthref450, however, states the following: "Elements of a nested table are actually stored in a separate storage table.... Oracle stores nested table data in a single storage table associated with the object table for both nested table types that are columns in a relational table or attributes in an object table. The storage table contains a column that identifies the parent table row or object that each element of the nested table belongs to....The NESTED TABLE..STORE AS clause specifies storage names for nested tables. Storage names are used to create an index on a nested table." If this last page had definitively referred to the whole "NESTED TABLE...STORE AS" as the "storage clause," I would reject P.H.'s claim of ambiguity. But I cannot do this. So I will give everyone credit for a correct answer on this choice, and change the text to make it unambiguous. I am currently in Dallas for the Oracle PL/SQL Programming/APEXposed conferences, so I may not update scores and ranks until I get back home on Friday. Congratulations to P.H., the only player who noticed this ambiguity and who wins an O'Reilly Media ebook.

You feedback needed: how best to show "you got right/wrong" info for past quiz?

In response to player requests, in the 1.7 upgrade of the PL/SQL Challenge, we now display for each quiz choice detailed information about how you did compared to other players.

The text will look like this:

You got it right
76% got it wrong.

or

You got it wrong.
42% got it right.

In other words, we display the % of people who answered differently from you. I thought this would be a nice way to highlight your relative performance.

One player, at least, does not like this, writing: "While I was going through the results, I noticed that the statistics on how well the others are doing (23% of all players got it ....) sometimes says 'wrong' and sometimes 'right'. It would be more consequent if they always say 'right' (or 'wrong' if you prefer that option). Now I must do calculations in my head (arghhh...) after reading the text and interpret what was the sentence to compare how well the others did on the answer and to see how stupid I was in selecting the wrong answer compared to the others."

Does anyone else have any strong (or otherwise) opinions about this?

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