Typo in 17 September quiz leads to rescoring (1385)

Well, no excuse or explanation for this one beyond sheer sloppiness. One of the choices, scored as (and very much intended to be) correct, was this:

BEGIN
  SELECT my_sequence.NEXTVAL into l_value
    FROM SYS.dual;
END;

Sadly, I lopped off the declaration section in my copy/paste operation, and so this block of code would not execute successfully. I will, therefore, take the following actions: 1. Give everyone credit for a correct selection on this choice. 2. Fix the choice so that it includes the declration section. 3. Re-score and re-rank. Vandu wins an O''Reilly Media ebook for noticing and reporting this mistake. My apologies, Steven

Players offer another, better answer to the 16 September quiz (1384)

This quiz was focused on the use of the EXISTS method to avoid a NO_DATA_FOUND exception when trying to "read" an element at a given index value. But all the choices showed ways of looping through the contents of a collection. Several players wrote to remind me that if you want to loop through a collection that is sparse, the best way to do so is to use the FIRST, LAST, PRIOR, NEXT methods. In other words, instead of this:

DECLARE
  l_sons   ages_pkg.sons_t;
BEGIN
  l_sons (19860923).name_of_son := 'Eli';
  l_sons (19860923).age_of_son := 23;  
  l_sons (19720425).name_of_son := 'Chris';
  l_sons (19720425).age_of_son := 38;

  FOR indx IN l_sons.FIRST .. l_sons.LAST
  LOOP
     show_age (l_sons, indx);
  END LOOP;
END;

You would write:

DECLARE
  l_sons   ages_pkg.sons_t;

l_index  PLS_INTEGER;
BEGIN
  l_sons (19860923).name_of_son := 'Eli';
  l_sons (19860923).age_of_son := 23;  
  l_sons (19720425).name_of_son := 'Chris';
  l_sons (19720425).age_of_son := 38;

  l_index := l_sons.FIRST;

WHILE (l_index IS NOT NULL)
  LOOP
  LOOP
     show_age (l_sons, l_index);

l_index := l_sons.NEXT (l_index);
  END LOOP;
END;

Here are the remarks submitted by Jeff Kemp: Steven, you wrote "Generally, the best way to handle such situations is to call the EXISTS method to verify that am element exists at the specified index value, thereby avoiding the raising of a NO_DATA_FOUND exception" Certainly, this is one way to solve the problem, but when I am dealing with a sparsely-populated array I prefer to forego the FOR LOOP and use the .NEXT property of the array to jump from each index directly to its successor. (Alternatively, there is also the INDICES OF syntax for the FOR LOOP that also avoids all the extraneous EXISTS checks) If there are hundreds of millions indexes between two entries in the array, your loop will test the EXISTS method for each and every one of them; sure, on modern processors this will not take much time, but I'm willing to bet (and I think I will test this when I get a chance) that a measurable performance improvement could be had by avoiding all the unnecessary EXISTS checks. Just my $0.02 :) I agree completely. I should have pointed that out, and will do so in the answer text. Anyone else care to comment? SF

Typo in 15 September quiz - but no need to rescore (1383)

In the 15 September regarding the use of FORALL with more than one DML statement, two of the incorrect choices were even more incorrect than I had originally intended. They both ended with this line:

WHERE l_nums (indx);

but they should have been:

WHERE custnum = l_nums (indx);

This typo does not affect the scoring of the quiz, but I did not intend it to look like this. So I hereby award a prize of an O'Reilly ebook to Frank Schmitt for pointing out this typo. I will fix the text in the choices to reflect my original intention.

14 September quiz: What about the extra line in FORMAT_ERROR_STACK? (1382)

The 14 September quiz asked: Which of the following blocks display (without the quotation marks) "ORA-20000: Balance too low!"? Several players wrote to remind me "None of the answers will display JUST the indicated message since there will be an extra CR/LF at the end (one for FORMAT_ERROR_STACK and one for PUT_LINE)." I agree and hopefully we can all agree: the DBMS_UTILITY.FORMAT_ERROR_STACK function returns the error stack, plus an additional line feed character. And if I had asked "Which of the blocks display absolutely nothing more than "ORA-20000: Balance too low!" then I suppose I would have to score all choices as wrong. Since I only asked which would show that string (and not just that string), though, I believe the scoring is correct. And I sure hope that going forward I will write my questions and choices so that this additional character will never play a role in determining whether a choice is correct or incorrect. Cheers, Steven

Update regarding past quizzes, forgiveness and rankings

Past Quizzes

First some great news: you can now view past quizzes up to 90 days in the past (up from 14). In an upcoming release of the PL/SQL Challenge, I intend to completely revamp this aspect of the website and give you much more flexiblity. For now, however, you can at least go waaaaaay back.

Forgiveness Update

I published a forgiveness policy several weeks ago, which is documented on the Rules page of the PL/SQL Challenge website. I have been working on applying that policy over the last week. Why is it taking so long? Because I want to make sure the impact of the forgiveness policy is appropriate. For example, the forgiveness policy should reduce the impact of missing quizzes, but the correction I make for missing a quiz should not allow you to leap far ahead of people who did play the quiz. I almost done with my tweaks and should have the policy applied by end of the week.

This will include a "provisional" adjustment in scores for "lowest ranked" days. I will re-run this adjustment at the end of the quarter before I select the playoff participants, but this will give you an idea of how that last adjustment will affect rankings.

Believe me, I am not trying to make things overly complicated. They are just turning out that way, for a variety of reason.

Questions on Ranking Data

In addition, players have reported some seeming aberrations in the rankings data (no one is credited with 100% playing of quizzes for this quarter; some ranking data is missing).I am working on figuring all of this out, but my time is severely constrained and I have to prioritize what gets my attention. So it may take a little while.

Warm regards,
Steven

Official Announcement: PL/SQL Challenge at Oracle OpenWorld / Oracle Develop

I have posted several variations of explanations for how this special competition will work next week. Here are the final, official details. Bottom line: lots of great prizes to win next week for playing the PL/SQL Challenge next week. Please pass this information on to anyone you know attending the conferences in San Francisco - and everyone else who works with PL/SQL!

Warm regards, Steven Feuerstein

PL/SQL Challenge (a daily quiz for PL/SQL developers) will be holding a special challenge during the week of Oracle OpenWorld and Oracle Develop. From September 20 through September 22 (with start and end of days determined by UTC time), you take the daily quiz through the PL/SQL Challenge website: www.plsqlchallenge.com. We will have two prize pools; one for Oracle OpenWorld and Oracle Develop attendees (with prizes provided by PL/SQL Challenge and O'Reilly Media)and another for non-attendees (playing from anywhere in the world).

If you are attending Oracle OpenWorld or Oracle Develop when you submit your answer, be sure to check the box labeled "I am at Oracle OpenWorld/Oracle Develop and can pick up my prize at the Mason Street Tent."

After the quiz ends on Wednesday, September 22^nd, prizes will be awarded both for top ranking (highest scores for the three days of quizzes) and prizes will be raffled off for all those who took at least one quiz (the more you take, the greater your chance of winning).

We will announce the winners no later than midnight in San Francisco, 22^nd September, with the list available on the PL/SQL Challenge blog, the Oracle Develop blog, Steven Feuerstein's blog, and on the PL/SQL Challenge website.

Winners of the Oracle OpenWorld and Oracle Develop prize pool then visit the Mason Street Tent Thursday, September 23, anytime between 7:00 am - 4:30 pm to pick up your prizes at the Oracle Technology Network info desk. I will be present from noon to 1 PM to congratulate you, sign books, and join you in a celebratory photo - if you so desire. If you do not claim your prize Thursday, September 23 by 4:30 PM it will be forfeit.

For those not attending Oracle Open World or Oracle Develop, the PL/SQL Challenge celebrates this enormous gathering of Oracle technologists by offering a set of special prizes for the week starting September 20. Instead of the usual two prizes for high rank and participation in that week, you could win any of the following:

• Complimentary registration to the ODTUG Kaleidoscope 2011 conference, a US$1775 value.
• Complimentary registration in your choice of the Oracle PL/SQL Programming/APEXposed conferences, a US$600 value
• Complimentary membership in the ODTUG organization, a US$175 value.

Remember: you do not need to attend Oracle OpenWorld or Oracle Develop to participate in this special competition, but if you are in San Francisco, you could win a special, attendee-only prize -so be sure to check that box when you submit your answer!