Change in Percent Corrrect May Be Noticed

A player wrote to me this morning:

My lifetime correctness has been around 75% for the last several weeks. Suddenly, when I check it this morning, it's dropped to 67.9%. Something is definitely odd, since I've been over 90% correct this week.

So what is going on? We finally fixed a bug in the way that % correct was being calculated for those "at most one right" questions.

A couple of months ago, we introduced a new "flavor" of question: when the choices are presented are clearly mutually exclusive, the scoring proceeds differently. Either you pick the correct answer and are 100% correct, or you picked the wrong answer and you get a score of 0.

This is different from other questions in which more than choice might be correct, and you get credit for both choosing the right answer(s) and not choosing the incorrect answer(s).

But with "at most one correct" questions, it's more of an all or nothing deal. Our calculation of % correct statistics did not, however, "keep up" with this alternative question style. We were still, say, showing 50% correct or 33% correct, even though you got a score of 0.

Which caused confusion for players and just didn't make sense.

So now you are recorded as either 100% or 0% correct for such questions, which is why some of you will see a reduction in your overall correctness numbers.

Cheers, Steven

Penalizing players for bugs or undocumented behavior? (2906)

The 15 June quiz tested your knowledge of the ability to pass arguments to parameterized cursors.

We scored as correct the following choice:

DECLARE
   l_filter   VARCHAR2 (100) := 'M%';

   CURSOR parts_cur (filter_in IN OUT VARCHAR2)
   IS
      SELECT *
        FROM plch_parts
       WHERE partname LIKE filter_in
       ORDER BY partnum;

   l_part     plch_parts%ROWTYPE;
BEGIN
   OPEN parts_cur (l_filter);

   LOOP
      FETCH parts_cur INTO l_part;
      EXIT WHEN parts_cur%NOTFOUND;
      sys.DBMS_OUTPUT.put_line (l_part.partnum);
   END LOOP;
   CLOSE parts_cur;
END;

even though Oracle documentation specifies that a cursor parameter may only be of mode IN (not IN OUT).

As we noted in the answer: "This choice specifies IN OUT for the mode. Oracle documentation only shows the use of the IN mode, but Oracle doesn't complain about IN OUT, and it certainly doesn't change the value of the variable."

A player wrote with this complaint:

This choice was not supposed to work, both by Oracle PL/SQL Documentation below: http://download.oracle.com/docs/cd/E11882_01/appdev.112/e17126/static.htm#CIHFAHFE http://download.oracle.com/docs/cd/E11882_01/appdev.112/e17126/explicit_cursor.htm#i33637 ( the railroad schema explicitly shows IN parameter mode as the only legal one ). and also even more explicitly by what is stated in your book: PL/SQL Programming 5-th edition, Chapter15, Working With Explicit Cursor, Cursor Parameter Modes, page.491-492, where it is clearly stated that: "You cannot specify OUT or IN OUT modes for cursor parameters". This being said and completely logic, it is however strange that both choices [6938] and [6939] do compile without error! Regarding choice 6938, since it also returns the expected result ( !!! ). I think that this choice should be rescored for ALL players and considered as INCORRECT. The same is in fact required for choice 6939, but that one is incorrect anyway (it does not display the expected output), but in principle is as wrong as the previous one. I am pretty sure that many players will raise the same objection. This is not a feature not specified in the documentation, as we have encountered some in our quizzes, but clearly a behavior that contradicts the documented (and logical) one.

And my response: first, it sure is a drag when someone quotes your own book at you! :-) And in fact what this player has pointed out is a bug in my book. In fact, you can specify OUT or IN OUT. Oracle does not stop you from doing this; the documentation simply does not indicate that it is accepted by the compiler. So I will fix that in my book.

Second, if the only correct choice in this quiz was the IN OUT parameter (and it was a "one choice correct only" question), then you should all be upset with me; that would be a "trick question." It is, however, just one of several correct choices. So, yes, you are "penalized" if you simply checked the documentation and answered according to it, rather than actually trying it out before you submitted your answer. But now you also know this is possible and it may be something you look out for in your code - that is, it may be "lurking" in your code and should be removed.

And that is part of the point of the PL/SQL Challenge - to extend your awareness of the day-to-day realities of the language. In conclusion, I do not believe a re-scoring is in order.

SYS.DBMS_OUTPUT.PUT_LINE - why do we include "SYS"?

I've been having a discussion with some PL/SQL Challenge reviewers regarding the use of the SYS. prefix in calls to DBMS_OUTPUT.PUT_LINE in the quizzes. We don't always include the prefix (mostly because I only recently started following this best practice - more on that below - in recent quizzes), but you will see it more and more.

One reviewer pointed out that a guideline for writing a good quiz is "Don't include unnecessary information." and this seems to be unnecessary.

Well, maybe, but also a little bit less secure. I have started prefixing SYS. in front of built-in package calls after reading Bryn Llewellyn's excellent white paper, "Doing SQL from PL/SQL: Best and Worst Practices", available here. One of his principles is:

Principle_4: References to objects that Oracle Corporation ships with Oracle Database should be dot-qualified with the Owner. (This is frequently, but not always, Sys.) This preserves the intended meaning even if a local object, whose name collides with that of the intended object, is created in the schema which will be current when name resolution is done.

Of course, you are unlikely to create a local package named DBMS_OUTPUT. But what if someone "breaks into" your database and injects a package with that name? It will effectively hijack all calls to the subprograms in the package.

So that's the rationale for putting SYS. in front of calls to DBMS_OUTPUT.PUT_LINE.

Your thoughts?

Promises Kept - the SQL Quiz Cometh!

Well, for the last couple of months, we promised that when PL/SQL Challenge v2 arrived, we would start up SQL quizzes, APEX quizzes, all sorts of great stuff.

And we will. Really. It's just that we've been kind of busy "burning in" all the new features and especially backend management utilities.

But enough. It's time to start keeping those promises.

So I am pleased to announce that the weekly SQL quiz will start this Saturday, 18 June.

Weekly quizzes start on Saturday and end on Friday at midnight (UTC time of course).

Please spread the word to all the Oracle technologists in your life who do not give a hoot about PL/SQL. Surely, they'd like to demonstrate their expertise in SQL (or learn some great new tricks)!

And as I have said previously: I am not competent to write SQL quizzes, except the most basic sort. So I strongly encourage you to submit your quiz ideas.

Finally, of course some of you are also wondering about that weekly APEX quiz. We should have an announcement about that within the next few days. Seriously. :-)

Cheers, Steven Feuerstein