28 July 2011

Passing non-numeric strings to number arguments (4507)

The 27 July quiz asked the following:

I create the following function in my schema:

CREATE OR REPLACE FUNCTION plch_num_diff (number_in IN NUMBER)
   RETURN PLS_INTEGER
IS
BEGIN
   RETURN CEIL (number_in) - FLOOR (number_in);
END;
/

Assuming that the value passed for number_in to this function is never NULL, which of the statements about this function are true?

A player wrote with the following objection:  "I think this question is ambiguous. It is not clear about the scenario where a user passes a non-number value into the function."

I don't think this is a valid objection for this quiz because if you pass a string that cannot be implicitly converted into a number for the argument to plch_num_diff, the function will not be run. Instead, Oracle will raise a VALUE_ERROR exception when it attempts the conversion (and before the function itself is executed).

This can be demonstrated as follows: I try to call plch_num_diff with an argument value of 'abc'. Then I trap any exception and display the error message and backtrace.
DECLARE
   l_value   NUMBER;
BEGIN
   l_value := plch_num_diff ('abc');
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (sys.DBMS_UTILITY.format_error_stack);
      DBMS_OUTPUT.put_line (sys.DBMS_UTILITY.format_error_backtrace);
END;
/
Here's the output I see:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error

ORA-06512: at line 4
Notice that the backtrace does not show an error raised inside the function. Contrast that to the output from the following statements. I now raise an exception insde plch_num_diff and pass it a valid numeric value.
CREATE OR REPLACE FUNCTION plch_num_diff (number_in IN NUMBER)
   RETURN PLS_INTEGER
IS
BEGIN
   RAISE VALUE_ERROR;
   RETURN CEIL (number_in) - FLOOR (number_in);
END;
/

DECLARE
   l_value   NUMBER;
BEGIN
   l_value := plch_num_diff (1);
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (sys.DBMS_UTILITY.format_error_stack);
      DBMS_OUTPUT.put_line (sys.DBMS_UTILITY.format_error_backtrace);
END;
/
And now I see the following output, indicating that the error was raised within the function:
ORA-06502: PL/SQL: numeric or value error

ORA-06512: at "HR.PLCH_NUM_DIFF", line 5
ORA-06512: at line 4
I therefore conclude that the behavior of PL/SQL in a block invoking plch_num_diff has nothing to do with the functionality of the function itself, which was the focus of the quiz.

27 July 2011

Q2 2011 Championship Playoff Results

You will find below the results of the Q2 2011 championship playoff. You can take a look at the quizzes that were in the competition through the Library page. These results will also be available through the Rankings page.

Congratulations to all players, of course, but let's give a special round of applause to our top three players:
  • Gary Myers (#1), who also ranked #2 in the last playoff and who wins a US$250 Amazon.com gift card. His first place ranking is especially impressive given the short time Gary took to answer the questions.
  • Chris Saxon (#2), who wins a US$175 Amazon.com gift card
  • João Barreto (#3), who wins a US$100 Amazon.com gift card
Good luck to everyone in the quizzes for Q3 2011 - I hope to see you in the next playoff!

Rank Name Country Total Time Total Score
1Gary MyersAustralia11 mins 21 secs2848
2Chris SaxonUnited Kingdom19 mins 32 secs2734
3João BarretoPortugal15 mins 06 secs2598
4Viacheslav StepanovRussia19 mins 57 secs2551
5Frank SchraderGermany19 mins 50 secs2353
6Ludovic SzewczykBelgium18 mins 35 secs2328
7Siim KaskEstonia18 mins 53 secs2322
8james suCanada16 mins 28 secs2321
9kowidoNo Country Set19 mins 49 secs2254
10Mike PargeterUnited Kingdom16 mins 41 secs2216
11Sean StuberUnited States20 mins 00 secs2150
12Randy GettmanUnited States18 mins 58 secs2146
13Filipe SilvaPortugal18 mins 04 secs2139
14owbegUkraine15 mins 48 secs2109
15Dalibor Kova Croatia18 mins 37 secs2053
16Jeff KempAustralia11 mins 38 secs1942
17macabreRussia19 mins 41 secs1931
18Theo AsmaNetherlands14 mins 48 secs1829
19Mojibul HoqueBangladesh03 mins 57 secs1821
20Sayan MalakshinovRussia04 mins 32 secs1809
21Kevan GellingIsle of Man18 mins 28 secs1781
22Anna OnishchukIreland17 mins 12 secs1706
23DikkieDickNetherlands13 mins 58 secs1696
24Alexander PolivanyUkraine20 mins 00 secs1675
25mentzel.iudithIsrael20 mins 00 secs1575
26Marc ThompsonAustralia19 mins 39 secs1557
27QBelgium16 mins 12 secs1551
28cleeUnited States17 mins 56 secs1441
29NickLUnited Kingdom19 mins 55 secs1402
30Pavel ZemanCzech Republic20 mins 01 secs1375
31Gerry171United States19 mins 00 secs1345
32Rajesh VenkataramaniIndia16 mins 43 secs1341
33RosemaryUnited States18 mins 38 secs727
34Jerry BullUnited States18 mins 47 secs40

24 July 2011

Objections to PL/SQL warnings quiz in Q2 2011 playoff (4483)

The Q2 2011 playoff tested players' knowledge of Oracle's compile-time warnings, in particular some key warnings added in Oracle Database 11g Release 2. In this quiz, we showed a program unit and asked how many warnings would be displayed by Oracle.

Players had the following objections:

"I hope I never see another question like this again! I can't imagine how anyone could have gotten this one correct without actually typing in and running it. I would have preferred if the question was in the form "which of the following warnings would be shown", and the answers would include these and perhaps other unrelated warning messages."

"I think that this quiz was somehat unfair towards players that are using 11gR1. As by my test performed with 11gR1 (11.1.0.7.0.), there are only 4 warnings generated, namely: PLW-7203, PLW-5004, PLW-6009 and PLW-5005. The additional two ones, specific for 11gR2 are even not mentioned in the PL/SQL Documentation of 11gR2, nor in other sources, like your book PL/SQL Programming 5-th Ed, so those who are NOT using this version were unable to know about it. By the way, there is no consistent source that offers a complete list of all these warnings, for any version. So, I think that for the sake of fairness, this quiz should be rescored and considered as correct any answer that fits the previous releases, 11gR1 and 10gR2. I did not check 10gR2, it may be the same as 11gR1."

I can see why this quiz would have been a source of frustration and perhaps irritation, but I don't see why I should rescore.

The Oracle documentation of every version offers an Error Messages book, which contains (so far as I know) a comprehensive list of warnings. This quiz specified 11.2 as the minimum version.

So to answer the question correctly, you needed to (a) read the code and identify fairly obvious problems with it; (b) be generally conversant with the way compile-time warnings work; and (c) either be aware of the new warnings added in 11.1 and 11.2 or scan the documentation for verification of your analysis.