Advance Notice: "Very Fast Answers" adjustment coming up

After the end of the month, I will be running my "very fast answers" algorithm and adjustment for the period 15 August through 31 August.

To recap: for each I record the amount of time it takes me (author of the quiz and PL/SQL expert) to answer the quiz.

This then becomes the baseline for analyzing the time taken by players.

If your total time is below mine (with a grace period thrown in), I increase your "time to answer". I then re-score and re-rank. And then I pick the winner for "Most Correct of the Month."

Generally what happens is that if your answer time is so aggressive that it mimics the answer time of someone who might be cheating (using two different accounts to get an advance peak and study of the quiz), you will be pushed down in the rankings.

Regards, Steven Feuerstein

Problems with 27 August quiz - undocumented features, ambiguous wording (1365)

The 27 August quiz tested your knowledge of the ALL_PROCEDURES data dictionary view, which gives you information about stored program units on which you have EXECUTE authority. There were a few problems with this quiz: 1. The ability to check the type of the program unit (through a column named OBJECT_TYPE) was missing in early releases of 10.2, including the Oracle XE version (10.2.0.1) and was undocumented in 10.2 documentation. Undocumented features do not concern me too much - you should run some code or connect to your instance to see what is really going on or is available, but it is a bit much to expect you to be aware of sub-release patch numbers in which a feature is added. 2. One of the multiple choices scored as correct was "This view allows you to determine whether a schema-level program unit is a function or procedure." Yet this is not really the case. A schema-level program unit could be a function, procedure, trigger, package or type. 3. The OVERLOAD column referenced in one of the answers was only added in Oracle11g, though there is a way to figure out whether or not a subprogram was overloaded through a query like the following (thanks, Sebastian, for providing this!):

select object_name, procedure_name
from all_procedures
where object_name = 'YOUR_PACKAGE_NAME_HERE'
having count(*) > 1
group by object_name, procedure_name;

Between the documentation/release issues of #1 and the semantical problems of #2, I have decided to give everyone a score of "correct" for this choice: This view allows you to determine whether a schema-level program unit is a function or procedure. I will then re-rank for the week and, finally, choose the winners of the week. Sebastian Kolski and Jeff Kemp both receive a prize of an O'Reilly ebook of their choice for pointing out these issues. I will also change the text of the OVERLOAD answer to add the query for 10.2 databases, and tighten up the language of the choice on which I am rescoring. Cheers, SF

"No commit!" observed several players re: 25 August quiz (1363)

Three players wrote with a concern: the code in the 25 August quiz question inserts three rows into a table, does not commit and then asks: "Which of the following blocks display "1" (without the double quotes) after execution?" Here is the most thorough comment submitted on this: "Today's quiz is somewhat questionable as its 'create/populate' section does not contain COMMIT after inserts and it is not specified explicitly that PL/SQL blocks in question are executed in the same session." Now, it is true that I usually do include a COMMIT; and I often also include the word "then" in the final question about "which choice," to give a sense of immediacy to the process: I create the table, then I run this block. Having said all that, I do not think there was a lot of room for misinterpretation here. It would seem to me that the obvious and completely reasonable interpretation is that the various blocks ran in the same session immediately after creating and populating the table. So I do not plan to change any scores in response to these comments. Anyone feel differently?

PL/SQL Challenge Live event in Melbourne, Australia

Quest Software and AUSOUG sponsored a PL/SQL Challenge Live quiz at the Insync 2010 conference on 16 August (which took place just 3 hours after I arrived from a very long odyssey from Chicago). This is the second live quiz I have done (the first was at ODTUG's Kaleidoscope conference in June), and it went much smoother than the first, since I built a PL/SQL procedure to automatically compute winners (manual processes really are things to avoid whenever possible!).

Forty-two developers played the quiz and I am pleased to announce that Jeff Kemp, currently ranked #5 in this quarter on the daily quiz, took first place with a score of 37 points out of a possible maximum score of 43. He wins a $100 giftcard from Westfield Mall. Congratulations, Jeff!

Here is list of the top 20 scorers in the competition:

01 Player JEFF KEMP = 37
02 Player STUART WATSON = 34
03 Player INDU NEELAKANDAN = 33
04 Player TREVOR TRACEY-PATTE = 33
05 Player BRETT MCBRIDE = 32
06 Player MARIA DE MESA = 32
07 Player KHOI NGUYEN = 32
08 Player IAN CORKHILL = 31
09 Player MARC THOMPSON = 31
10 Player ROHAN MILTON = 31
11 Player SWEE KEONG TAN = 31
12 Player RAY FEIGHERY = 31
13 Player GARETH OWEN-CONWAY = 30
14 Player TIM DANIELL = 30
15 Player ASHOK SHOKKANNAA = 30
16 Player THOMAS WENDE = 29
17 Player PANKAJ DADOO = 29
18 Player ROGER SCHNEIDER = 29
19 Player ANTHONY BRUMBY = 29
20 Player R F = 29

Hopefully they will all register at and play the daily quiz of the PL/SQL Challenge as well. They clearly have lots of promise!

Finally we were lucky enough to have Tom Kyte in attendance at the live quiz. He kindly pointed out two subtle problems with the quiz, so I cleaned those up for the other quizzes later in the week. One point will also result in a minor change in our assumptions.

A question re: TRANSLATE from player

I received this email last week:

Steven, would you mind giving me a explanation about how TRANSLATE works. The two blocks do not seem to work in a consistent way:

DECLARE
   my_formatted_number   VARCHAR2 (100) := '00001233.056';
BEGIN
   DBMS_OUTPUT.put_line (TRANSLATE (my_formatted_number, 'A0', 'A'));
END;
 
Output: 1233.56

DECLARE
   my_formatted_number   VARCHAR2 (100) := 'Example';
BEGIN
   DBMS_OUTPUT.put_line (TRANSLATE (my_formatted_number, 'Ee', '98'));
END;

Output: 9xampl8.

I am glad that this player took the time to ask about this, because it reminded me that I had not provided all the information I could have in the quiz in which this topic came up (19 August). So I will answer the question here and then add more content to that question.

You see, what I did with that TRANSLATE (my_formatted_number, 'A0', 'A') is a bit of a "trick." This is the header of TRANSLATE:

  function TRANSLATE(STR1 VARCHAR2 CHARACTER SET ANY_CS,
                     SRC VARCHAR2 CHARACTER SET STR1%CHARSET,
                     DEST VARCHAR2 CHARACTER SET STR1%CHARSET)
        return VARCHAR2 CHARACTER SET STR1%CHARSET;

TRANSLATE replaces every occurrence in STR1 of the character in position N of SRC with the corresponding character in position N of DEST.

Now suppose that I want to replace a character with nothing - get rid of that character from the string - say, remove all zeros. I might think to do something like this:

TRANSLATE (my_formatted_number, '0', NULL)

The problem with doing this is that if any of the arguments passed to TRANSLATE is NULL, then TRANSLATE always returns NULL.

So you have to play a little game: change the SRC argument value to contain two letters. The first character can be anything you want, the second chraacter is the one you want removed. Then pass just the first character as DEST, as in:

TRANSLATE (my_formatted_number, 'A0', 'A')

What I am now saying is: change A to A and change 0 to NULL.

And that is why the code you showed me above works as it does.

Regards, SF