15 October: MINUS "works" but it is "minus" common sense (1503)

The 15 October quiz offered this choice as a correct, but "overkill" solution:

CREATE TABLE parts_copy
AS
  SELECT * FROM parts
/

BEGIN
  UPDATE parts
     SET part_name = part_name || '1';

  FOR rec IN (SELECT part_number, part_name FROM parts
              MINUS
              SELECT part_number, part_name FROM parts_copy)
  LOOP
     DBMS_OUTPUT.put_line (rec.part_number);
  END LOOP;
END;
/

Vitaliy wrote to say that "I suspect that you marked choice containing MINUS as correct. I spent reasonable time staring at it. At last I intentionally marked it as incorrect. Although choice displays what it updates, that is just a visual coincidence. It would be worst practice: - MINUS perform DISTINCT and table doesn't have a unique constraint; - in multi-session concurrent environment new data can arrive." After you saw my scoring, you followed up with: "The choice is not just inefficient solution. It is indeed delayed-action mine....As advanced PL/SQL developer I couldn't force myself to mark it as correct Maybe I already have outgrew the challenge's boundary?" Vitaliy, I admire your principles. You took a stand against code that would solve the quiz but could lead to big problems in one's application. I will change the explanation of that choice to point out the more serious dangers with this approach. I certainly hope you do not stop playing the Challenge and offering your insights. Cheers, SF

14 October quiz: What if the table already exists? (1507)

Great minds clearly think alike. In the 14 October quiz, I asked you to choose the blocks that would display "1" when executed. One of the correct choices was:

DECLARE
  l_count   INTEGER;
BEGIN
  DBMS_UTILITY.EXEC_DDL_STATEMENT ('CREATE TABLE plch_table (n NUMBER)');

  SELECT COUNT (*)
    INTO l_count
    FROM user_tables
   WHERE table_name = 'PLCH_TABLE';

  DBMS_OUTPUT.put_line (l_count);
END;
/

Several players raised a concern best expressed by MNL: 'It is not possible to answer today's quiz without knowing if the table plch_table exists in advance. If it exists, none of the choices display "1" because the possible exception from the table creation is not handled, which is an often seen mistake. To avoid any confusion you should add the general assumption that the code is executed on an empty schema.' I must admit that I was very surprised to see these responses, but not because it isn't a valid concern. It is. Certainly, whenever you execute DDL like this in your programs, you do need to either check first to see if the table exists, or trap the error that would occur if it did exist. Now having said that, I was surprised, because I had thought that we did have an assumption in place covering this. It read as follows:

'The session and the environment in which the quiz code executes has enabled output from DBMS_OUTPUT, and can reference only those datatypes, programs and database objects defined in the context of the quiz or are available in a default installation of the Oracle "instance."'

If you can't reference it from your schema, it doesn't exist, right? Well, clearly, this assumption was not clear enough for some players. So I have now changed it to:

'The only database objects and PL/SQL program elements ("things" that can be referenced in the quiz code) that exist are those defined in the context of the quiz or are available in a default installation of the Oracle instance.'

Does anyone feel that this needs to be adjusted further to avoid any confusion? Regards, SF

Players tell Steven: "Clean up after yourself!"

The 13 October quiz on cursor variables featured this block:

DECLARE
   l_data   SYS_REFCURSOR;
   l_row    plch_parts%ROWTYPE;
BEGIN
   /*OPEN*/

   LOOP
      FETCH l_data INTO l_row;

      EXIT WHEN l_data%NOTFOUND;
      DBMS_OUTPUT.put_line (l_row.partname);
   END LOOP;
END;
/

and several players noticed that this block was missing a statement to close the cursor.

Very sloppy, Steven! While the lack of a CLOSE statement did not affect the quiz, it certainly reflects poor programming practice.

Thanks for bringing this to my attention. I will change the quiz question and answers to include a CLOSE.

Cheers, SF

Update regarding the Q3 Championship Playoff

On Tuesday, October 12, at 10 AM UTC, over 70 developers eagerly visited playoff.plsqlchallenge.com and pressed the buttons that would start their taking of the playoff.

Unfortunately, we encountered several technical difficulties, most likely related to the virtually simultaneous retrieval of quiz images and submission of answers by so many players.

While over 60 players were able to complete at least eight of the ten quizzes, many others could not move past the first couple of quizzes. So with great regret, we had to cancel the playoff and not use the results to pick our quarterly champions.

We are going to fix the bugs and run a live test of the playoff site before we re-schedule the playoff. It is very likely that we will hold playoff in November.

The playoff participants have been wonderful, both in their determination to work through the bugs to take the quizzes and also in their generosity of spirit. No one got really mad at me, which was good, because I was already plenty disgusted with myself for letting this happen.

We will make the results from the quiz available to participants next week, so they can see how they did. When we have fixed our bugs and are certain the playoff site will not implode, we will announce the date and time of the playoff.

In the meantime, the daily quiz continues - and I will very soon make an announcement regarding the upgrade of the PL/SQL Challenge site to version 1.7 and APEX4. You will, I promise, be delighted with the new features in this release.

Warm regards,
Steven

Questions regarding 12 October quiz and contents of nested table (1505)

The 12 October quiz asked the following: Assuming that l_table is declared as you see below, which of these Boolean expressions are always true, regardless of the contents of the nested table?

DECLARE
  TYPE numbers_t IS TABLE OF NUMBER;
  l_table   numbers_t := numbers_t ();

Several people have written to me about this (actually, on a different blog post - I will copy them below for clarity) as follow: "I completely mis-read today's quiz for October 12th, and looking at the average score, I am assuming everyone else did too. I thought all the choices referred to an empty table/array, even though in retrospect you clearly say otherwise. Live and learn, I guess." "Yes, the quiz would be much more clear if a comment like /* some operations on the l_table here */ will be present in the block." And two players put together a block of code to demonstrate that the way I scored the answers was incorrect for the empty nested table shown in the question. I thought it was rather clear that since (a) I do not include the executable section and (b) I say "always true, regardless of the contents of the nested table" (italics added), I am asking you which statement will be true regardless of what you do with the nested table in the executable section. Not clear enough, I suppose. I am not going to change the score (uh oh, perhaps others will stop playing the Challenge? Hmmm. It is 5:45 AM. Perhaps I am just irritable). I will, however, change the text of the question so that it is more clear in the future (I say this because I plan in the future for you to be able to take quizzes again, to practice and further sharpen your knowledge!). Regards, SF

Steven is humbled; 2nd adjustment for 11 October is made (1504)

I feel that, generally, I am pretty good at admitting that I am wrong. But for some reason, when it came to the 11 October quiz, I didn't want to admit that "see -100" was ambiguous, when combined with the actual output of the quiz, which displayed -100 twice. In any case, I have been humbled by the many comments on the previous post for this quiz. There is no doubt how many of you feel. I am not running the PL/SQL Challenge to show how right I can be, no matter what anyone else thinks. I am spending lots of my "spare" time on the PL/SQL Challenge to give you an opportunity to learn about and enjoy the PL/SQL language. So....with all my thanks and apologies, I will change the text of this quiz so that "-100" will only be displayed once. I will give everyone credit for a correct selection of the two choices that contain forward references for proc2, and I will recalculate scores. Then I will finish preparation for the Q3 championship playoff! Thanks to all of you for caring enough about the PL/SQL Challenge and your rank to make your views known. Regards, Steven

Correction for 11 October quiz - and change in policy regarding mistakes (1504)

The 11 October quiz contained a mistake - for the first four hour's of its life. I had neglected to append a ";" after the forward references in the choices, with the consequence that none of them would be correct. In the past, my policy had been this: once the quiz is published, it cannot be changed until the day is over. I have decided to change this policy. I will now fix a mistake as soon as it is discovered, usually very early after the quiz starts. That way, for the 1300+ developers who do not play the quiz in the early hours of the day (UTC time), there will be no mistake. And for the relatively few players who did play the quiz with the mistake, you will receive a correction to your score for all choices that were affected by the mistake. I did, in fact, add the ";" to the statements for the 11 October quiz and the scores of roughly 57 players will be corrected to reflect this. Chris Roderick wins an O'Reily Media for being one of the players who brought this problem to my attention. That wasn't the only concern raised by players. The question on 11 October asked, in part: 'Which of the choices offer substitutions for the /*PUBLIC*/ and /*PRIVATE*/ comments so that when the following block is executed, I will see "-100" (without the double quotes) on my screen?' In fact, the choices scored as correct each displayed -100 twice. Some of you felt that therefore all the answers were incorrect, others simply felt this was ambiguous. I disagree. You will definitely see "-100" on your screen. I do not specify that it is the only thing you will see. So there will be no change in scoring for this issue. Cheers, SF