The PL/SQL Challenge (www.plsqlchallenge.com) offers a daily quiz on the PL/SQL language, through which thousands of Oracle technologists demonstrate and deepen their knowledge of PL/SQL. This blog contains posts by the PL/SQL Challenge founder, Steven Feuerstein, as well as comments from players.

09 October 2010

Hi Steven, Why was the question on "08th Oct 2010" on an advanced level? I ask because everyone who works with Oracle object types feels this question is more like a beginner question (just initializing and assigning). If you don't mind, can you please share as how the questions were categorized as Beginner, Intermediate and Advanced?

I certainly don't mind. One of the aspects of the PL/SQL Challenge that I like best is its transparency.We make available all sorts of data about the quizzes and the answers people have submitted. This makes it possible for you to give me feedback, help me improve the PL/SQL Challenge, and see how other players are doing.

So, first, thanks for asking about this specific quiz. It should not have been marked as an Advanced question. As to how it got that way, I have generally been assigning a difficulty level of at least Intermediate for any areas of functionality that are rarely used, such as object types.

Your email has me re-thinking this approach, though. It seems like what I should do is rank the difficulty level within the context of the functionality area, at least for a feature as broad as object types. There are beginner questions on object types and, you are right - this is one of them.

So here's what I am going to do:

1. Change the difficulty level on this question to Beginner (everyone's scores will be decreased, but your rankings will not be affected).

2. Be more discriminating about how I set that difficulty level. It may be true that few developers use object types, but it is a part of the core PL/SQL language and has been available for years. There is no reason that we should not all be expected to have at least a rudimentary awareness of it.

08 October 2010

The question for the 7 October quiz was:
Which of the following blocks, when executed, satisfy these two requirements:

1. It will display the string "50" (without the double quotes).

2. Every statement in the block is required for the block to compile successfully.

My main objective with this lesson was to remind everyone that you do not need to (and should not) declare a variable for the iterator in a FOR loop. That is done implicitly for us by Oracle. And the way I decided to get the point across was to add that second requirement "Every statement in the block is required for the block to compile successfully."
That is, the choice in which that "extra" variable was declared was scored as incorrect:

This block of code avoids declaring l_iterator, and displays "50." Originally (up until more or less the "last minute"), I scored this as correct. Then, as I did my just-before-quiz-starts review of the quiz, I said to myself: "Wait a minute, I could remove that DBMS_OUTPUT.PUT_LINE call and the block would still compile successfully! So this is incorrect." So I changed the way I scored that choice. I think that this change would have made sense if the question did not say "when executed", but that clause clearly indicates that both requirements need to be made at the time the block executes. The lesson I learned (and re-learn, sadly)? Do not change your mind late in the process, certainly not without discussing the change, and motivation for it, with someone else.To conclude: this choice was scored improperly. I will change it from "incorrect" to "correct," and I will also change the explanation to explain clearly why this should be considered correct. And then I will update scores and re-rank.Congratulations to Oliver Dimalanta, one of over a dozen players who reported this mistake. Oliver receives his choice of an O'Reilly Media ebook.

07 October 2010

I plan for the 1.7 release of PL/SQL Challenge (tentatively scheduled for 23 October) to improve the quantity and quality of information shown on the Past Quizzes page.

Once this page is re-designed, I then plan to send out the same information in the "yesterday's quiz" email.

So if you have any thoughts about how you'd like to see this page and/or the daily email changed, please post your ideas here (or, if you do not have a Blogger/Gmail account, send them directly to steven@stevenfeuerstein.com).

05 October 2010

The 4 October quiz recorded as correct the following statement:
"You can declare a record based on a table, so that every time you compile the block in which the record is defined, the PL/SQL compiler defines a field in the record for each column in the table, with matching name and datatype."
One player wrote to me with the following comment (I bolded the key words):

Steven, in the explanation for this choice you write "You can declare a record based on a table, so that every time you compile the block in which the record is defined, the PL/SQL compiler defines a field in the record for each column in the table, with matching name and datatype." You show an anonymous block illustrated this, i don't know if this is "almost" true, coz i can create a procedure, compiled, modified the table, and i don't need to compiled again to take effect the %ROWTYPE. i think is more like "that every time you -execute- the block in which the record is defined, the PL/SQL compiler defines a field in the record for each column in the table, with matching name and datatype."

This is definitely not true. The "lookup" of the structure of the table, view or cursor, to then use to define the fields of a record type in PL/SQL definitely happens at compile time. When you are executing an anonymous block, the PL/SQL compiler must first compile that block before it is executed. That's when the lookup occurs. If you have defined a stored program unit, like a procedure with a %ROWTYPE declaration, then whenever that program unit is re-compiled, Oracle again "looks up" the structure of table, view or cursor.
Some of the confusion might arise from the fact that you do not have to explicitly recompile your program unit after you change a table referenced in %ROWTYPE. Oracle automatically marks that program unit as invalid and will also automatically attempt to recompile it the next time you try to use that program unit.
But there is no doubt that this is compile-time and not run-time behavior. There is no way that you or Oracle would want to slow down production performance by looking up the structure of tables and views every time the program unit is executed!
Cheers, SF

The English language (or, rather, my use of it) strikes again!
The 4 October quiz was one of those non-code, all-words quiz and it burned me/us again. The quiz question was:
"Which of the following statements about PL/SQL records are correct?"
Players complained about three (yes, three) of the choices. But I don't agree with all complaints. :-) Let's take a closer look....
1. "A record contains an audio or video recording for storage in the Oracle database."
As one player wrote: "I chose to read it as: 'A record may contain an audio or video recording...' Obviously, it is a column within the record that contains the multimedia experience, which means the record also does.'
My response to this interpretation is: I disagree. That is, I suppose I understand why you might read the choice this way, but I do not believe it is a reasonable interpretation - I don't think there is ambiguity here. The question makes it clear that I am asking you to identify correct statements about records in general. It is true that a record might contain a "recording"/BLOB field. But it doesn't have to and the choice clearly (or at least, clearly enough, to my mind). This statement is not true for "PL/SQL records" as the question asks. Furthermore, it should be well understood that a PL/SQL record is not necessarily stored in the Oracle database (as a row in a table). So this statement was incorrect on several counts. I will not change scoring for this choice.
Another player wrote about this choice: "I would have preferred this option to say 'A record ALWAYS contains a video/audio recording...' to remove the ambiguity." Yes, that would certainly be more explicit, but I also feel that it would "give too much away."
2. 'While it is possible to assign NULL to a record, it is not possible to check to see if every field in the record is null using the "IS NULL" operator.'
Sigh. Yes, here we have an ambiguity that I cannot explain away. I wanted you to know or learn that you cannot use the IS NULL operator with the record variable itself. My decision to include "every field in the record," however, clearly invites you to interpret this choice as correct, in that you can check the contents of each field using IS NULL (or, if a field is a record, you could in turn use IS NULL on each of its fields, etc.). I should have made it clear that I wanted the IS NULL to be applied to the record. I did not, and so I will give all players credit for this choice, change the text to make it more explicit, and change all of your answers to indicate "correct" for this choice.
3. "You cannot check for equality between two records using the "=" operator. "
One player suggested that this choice has the same ambiguity as #2. That is, you can use "=" to compare the values of individual fields of the record. I disagree with this interpretation. I believe it is quite clear that this choice asks you to decide if you can use the "=" operator with the records themselves, not their fields. I will not changes scores on this choice.
So, sorry about that and on to the next quiz! Fortunately, it has code in it. :-)
Cheers,
Steven Feuerstein

04 October 2010

Now that September has passed us by, it is time for the 2010 Q3 Championship Playoff!

I am pleased to announce (and offer my congratulations to) the following 81 participants in this playoff (rules for qualification may be found on the Rules page, and are either through ranking, correctness level or wildcard selection):

We had 48 participants in the first playoff; the number is much larger this time due to the algorithm for correctness participation. The #10 ranked player had a correctness level of just over 91%, which led to lots of other players being invited to the playoff. [Advance notice: I plan to tweak this algorithm for future playoffs to limit the number of participants from the correctness category. They should not overwhelm those who qualified via ranking.]

I plan to hold the playoff competition on Tuesday, 12 October 2010, at 13:00 UTC, which translates to:

6 AM in California
8 AM in Chicago
2 PM London
3 PM Most of Europe
6:30 PM in New Delhi
9 PM in Singapore
Midnight in Melbourne

I realize that this is not terribly convenient for everyone (OK, that is an understatement for West Coast USA and Western Australia). Finding a good time for everyone is fairly impossible with a completely global competition like the PL/SQL Challenge - and it's not nearly as painful for Australians as the first playoff (which was held two hours later).

All participants will receive an invitation by email, and the opportunity to inform me if they cannot play. If more than 20% of participants cannot play at this time, I will find another date and time for the playoff.