Many thanks to my reviewers!

As we finish up another quarter and roughly 60 more quizzes at the PL/SQL Challenge, I would like to take a moment and some bits and bytes to recognize my quiz reviewers.

Ken Holmslykke, who was a top-ranked PL/SQL Challenge player, volunteered to stop taking the daily quizzes (and any hope of winning all those cool prizes) so that he could take on the task of checking each of my quizzes, which are often quite a mess before they make it your screen.

Darryl Hurley, who has over the years supported my book-writing efforts with both content (the chapter on triggers in Oracle PL/SQL Programming) and feedback, has also been instrumental in, well, saving me from myself.

While I still occasionally make mistakes that "sneak by" these two fine Oracle technologists, there is no doubt in my mind (and I have the emails to prove it) that they have saved me from many more mistakes, typos and downright misunderstandings than you would have thought possible.

Ken and Darryl, you have my unending gratitude for the commitment of your valuable time to the PL/SQL Challenge!

Warmest regards,
Steven Feuerstein

Next Steps for Q3 Quarterly Championship

Today is the first day of the 4th quarter of 2010. This means that yesterday was the end of the third quarter, and that means that in roughly two weeks, we will hold the second quarterly championship playoff!

Over the weekend, I will be doing the following:

1. Apply a "too fast answer" adjustment for the last two weeks.

2. Generally review the quality of quiz data for the past quarter and make sure no problems exist (many players have noted, for example, that their ranking information indicates that they missed a quiz. Well, you didn't miss a quiz; instead, the quiz on 14 July was invalidated due to downtime on the site.).

3. Produce the list of qualifiers for the playoff (see Rules page for details).

I will then publish an announcement of players and tentative date/time for the playoff next week.

Warm regards,
Steven Feuerstein

Warning from player regarding 30 September answer (1444)

In the 30 September quiz on RETURNING, I scored as correct the choice that performed the update and then, separately, issued a query to get information back regarding the impact of the update. I wrote in the explanation: This solution issues the update and then in a separate SQL statement retrieves the part number of the row that was just modified - but only by reproducing the logic ("partname = UPPER (partname)") in the WHERE clause. This means that I have introduced repetition in my code, and also inefficiency (an extra context switch). This is logically equivalent to using the RETURNING clause, but definitely inferior to RETURNING. A player wrote to say: In your explanation of answer 3 for the September 30 quiz, you note that this solution is repetitious and less efficient than using the RETURNING clause. I suggest that it is even more important to note that this is only 'accidentally correct' - if the starting data had included other rows where partname = upper(partname) such as '2 GB RAM' then this code would not have worked. This is a good point, both specifically for this answer and more generally for the quizzes: in order to make the quiz questions and choices unambiguous, I construct very "small worlds" of data and code. I seek to demonstrate and exercise your knowledge on fairly specific features. This means that you should be able to trust the lesson of the quiz, but you should not adopt/follow the code samples as any sort of general pattern for a solution to your real-world problems. In the case of this choice in the quiz, I should be (and will update the answer so that I am) more explicit in drawing out the lesson here: If you use a SELECT after your DML statement to determine if the correct changes were made, you need to be very careful about how you specify the WHERE clause of your query to be sure that you identify the same rows that were (possibly) changed.

Missing RETURN in 28 September quiz - but no impact on correctness! (1442)

Several players noticed that the function I included in the 28 September quiz was missing a RETURN statement:

CREATE OR REPLACE FUNCTION partnum_from_name (
  partname_in IN plch_parts.partname%TYPE)
  RETURN plch_parts.partnum%TYPE
IS
  l_return   plch_parts.partnum%TYPE;
BEGIN
  SELECT p.partnum
    INTO l_return
    FROM plch_parts p
   WHERE p.partname = partnum_from_name.partname_in;
END partnum_from_name;

Yes, missing a RETURN statement, no doubt about it. This bothered several players, and with good reason. If and when this function was executed it would (if one row was found for this part name) raise the following error:

ORA-06503: PL/SQL: Function returned without value

So what's the problem? Here's what one player wrote: "The function defined in the quiz on 28-SEP-2010 is invalid because it does not have a RETURN clause." Well, actually, that is not so. The function is valid, compiles without error. So in the context of this quiz, the lack of a RETURN statement had no impact whatsoever on the correctness of the choices. The function was compiled, and since it contained a select from the plch_parts table, the dependency view would reveal that information - no matter the run-time problems that might be encountered when the function was executed. Still, it was not my intention to leave out the RETURN statement, so I will add it into the question text to avoid this distraction in the future. In addition, and somewhat to my surprise, several players thought that I had made a mistake when writing a SELECT statement as follows:

SELECT COUNT (*)
 FROM all_dependencies
WHERE     referenced_name = 'PLCH_PARTS'
      AND referenced_owner = USER
      AND referenced_type = 'TABLE' 

Specially, they felt that the word "USER" should have been within single quoates, as in:

SELECT COUNT (*)
 FROM all_dependencies
WHERE     referenced_name = 'PLCH_PARTS'
      AND referenced_owner = 'USER'
      AND referenced_type = 'TABLE' 

But that would only work if the name of the schema that owned PLCH_PARTS was "USER". USER, without single quotes, is a built-in function that returns the name of the currently connected schema. So all in all, nothing to change in the quiz scoring. Thanks for writing with your comments! Cheers, SF

24 September quiz prompts warnings from players (1403)

The 24 September quiz tested your awareness of string-indexed collections, and how you can use these to effectively change the sort order of the data in one's collection. Two players wrote regarding this quiz as follows: Daniel: "The solutions only work in very specific scenarios. If the text value was to be repeated, ie: 2 Michael Jordan entries, then only one of them would be displayed. So I don't think that solutions provided, other than the Insert/Select are very safe when dealing with large data sets." Patrick: "I would suggest to add a big warning to one of the answers of the question of 24-Sep. I know that the answer creating a table on the fly, inserting the data and selecting from there is syntactically correct, but I think it should almost never be used that way in a real program. The simple reason for that is that it's not multi user/session ready. If another use is currently using that function, a second user would get an error message when he tries to create the same table on the fly. Especially in web applications, most sessions are sharing the same database runtime users. I wanted to point that out, because many novice developers might take your examples as "facts" how a more complicated sort could be solved, but I think that answer needs a big warning that this is just a theoretical solution and should NOT be used in real world applications." Both Daniel and Patrick make good points (I have changed one of the answers to include Patrick's warning, in fact) and also make me realize how important it is for players to recognize that these quizzes will almost always be focused on particular features of the PL/SQL language. These features are inevitably presented outside the context of "real world" application development, in which many factors come into play. I will try to be better about including warnings in answers, when a theoretically correct solution could raise practical, real-world issues - but I also rely on and trust you, our devoted players, to let me know whenever you have a concern. Also, I am looking into how I can also present best practice oriented topics in the daily quiz in an unamibguous fashion ("best practices" inherently have a good does of subjectivity associated with them). If you have any thoughts about how I might do that, or specific ideas for quizzes, don't hesitate to let me know. I also plan to reorganize the blog so that it can be directly linked to the quizzes, allowing discussion of features and application of those features to be more visible to everyone playing the quiz. Cheers, Steven

ODTUG Winners for Oracle Develop week quizzes

I am pleased to announce the following winners of ODTUG-sponsored prizes for playing the quiz last week:

Complimentary registration to the ODTUG Kaleidoscope 2011 conference, a US$1775 value: Gunjan

Complimentary registration in your choice of the Oracle PL/SQL Programming/APEXposed conferences, a US$600 value: V Vandana Patel  and jaydeep cheruku

Complimentary membership in the ODTUG organization, a US$175 value: Gracelin Jeyananth

Congratulations to all winners! ODTUG will be following up with you provide instructions for redeeming your prizes.

Steven Feuerstein