Is typing and running code a form of cheating?

I received this email from a player today:

Is it okay to use sqlplus at all while taking a quiz? I know reading oracle documentation is acceptable, and typing the quiz in sqlplus verbatim is not allowed, but are we allowed to test basic concepts in sqlplus at all? I have been assuming it is not allowed. For example, I wasn't sure if IS OF type returned true if the value of the variable was the type or if the variable was the type. The documentation isn't always clear. If I was allowed to use sqlplus, I would have tested out a few statements to verify the expected outcome of that statement. 

The answer is: it is perfectly all right to use SQL*Plus, Toad, SQL Developer, PL/SQL Developer, etc. to write and execute code to help you answer the quiz.

You should not (and, in fact, you should not be able to) copy and paste code from a quiz, and then simply execute it. But as I note, you should not be able to do this; most quizzes present code as an image. If the code is accessible for copying, then it is not (or, shall I say, should not be) a competitive quiz (rankings and prizes) and we don't really care if you copy and paste.

But if you want to take the time to type in a block of code (or more), then execute that code to see the results, please go right ahead. The most important objective of the PL/SQL Challenge is to improve your knowledge of PL/SQL (and SQL and APEX and....). I can think of few better ways to do this then to write code. 

You will, of course, be "penalized" for taking the time to write code; your score will be reduced by the extra time required to do so. But it is definitely not cheating.

NLS Settings and Assumptions - Be Careful! (5187)

The 3 August quiz tested your knowledge of the way that Oracle performs implicit conversions, in particular in this block:

DECLARE
   n   NUMBER;
BEGIN
   n := '123.456' || 900;
   DBMS_OUTPUT.put_line ('X' || n || 'X');
END;
/

We reported the correct answer as:

X123.4569X

But several players wrote to say that when they tested our answers with the verification code, they received the following error:

ORA-06502: PL/SQL: numeric or value error: character to number conversion error

So did we score this quiz improperly? Another player offered an explanation:

Hi, stupid me... A quick test for yesterdays quiz resulted in "ORA-06502: PL/SQL: numeric or value error: character to number conversion error". And that's the answer I gave for the quiz. Today I received the results and it seems I got it wrong... Of course I got it wrong...I live not in America, so the NLS setting were not set accordingly. I know they are in the assumptions, but after 100 quizes of not needing them, they'll get out of your mind.

And that is precisely why the error was not raised in the verification code and why the quiz was scored correctly. The assumptions state that:

The following calls to DBMS_SESSION.SET_NLS define national settings in effect for a quiz, unless otherwise specified in the quiz. If your local environment uses different settings, you may execute these statements to ensure compatibility with behavior and output of executable code provided by the PL/SQL Challenge.

DBMS_SESSION.SET_NLS ('NLS_LANGUAGE' ,'AMERICAN');
DBMS_SESSION.SET_NLS ('NLS_TERRITORY','AMERICA');
DBMS_SESSION.SET_NLS ('NLS_CALENDAR' ,'Gregorian'); 

This player also offered the following suggestion:

Is it a good idea when asking a question depending to NLS settings, to repeat this in the question(So people who want to test it and not living in the US aren't disadvantaged)? Just an idea.

That is a good idea and we will strive to remember to do this.

Public Profile Settings for Your PL/SQL Challenge Account

A player wrote to us today:

I completed every daily PL/SQL challenge (as well as every Weekly SQL quiz) in July, 2011. Yet I did not receive the Achievement, "All Quizzes Taken in July 2011 (PL/SQL Challenge)" on my profile. Yet I notice that others have received this achievement. Please rectify. (Note: I did not take any of the APEX or Logic quizzes.) Thanks; your site is great.

I checked his public profile and found that he had not enabled the display of achievements. Now his accomplishment is visible to anyone who checks out his profile. And that reminded me that I should remind all of you:

While you can register by providing the absolute minimum of information, we allow you to set up your profile so that you display your technical experience, professional achievements, recommendations to others, and more. You also have complete control over what information is displayed and what is not.

I strongly encourage you to take a few minutes to visit your Account page, enter this information, and then click on the Public Profile Settings to ensure that others can what you have entered:


You can then decide which of the information from your profile you want to make public:

New Oracle Magazine Quiz Starts Today!

Today marks the start of a new quiz at the PL/SQL Challenge: the Oracle Magazine PL/SQL quiz. I write a column for Oracle Magazine and two issues ago, I started a series of articles that introduce PL/SQL to those who are just entering the world of the Oracle Database.

And starting with the Sept/Oct 2011 issue, we are including quiz questions within the article, which can also be answered at the PL/SQL Challenge site.

Of course, you don't need to read the article to take the quiz, but we hope you will do so.

One player has already submitted a question about this quiz, asking: "The Oracle Magazine quiz ends in 1613 days? Seriously?"

Yes, seriously.  Each issue of Oracle Magazine has a "shelf life" of two-three months (before the next issue comes out), but the magazine is also available online. I know from experience that many Oracle technologists read these articles well into the future.

As a result, one way in which the Oracle Magazine PL/SQL quiz is different from others is that it will stay open for a really long time - until 31 December 2015.

But we will be selecting raffle winners randomly from all players, well before that, and likely more than once.

Enjoy!
Steven Feuerstein

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.

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
1	Gary Myers	Australia	11 mins 21 secs	2848
2	Chris Saxon	United Kingdom	19 mins 32 secs	2734
3	João Barreto	Portugal	15 mins 06 secs	2598
4	Viacheslav Stepanov	Russia	19 mins 57 secs	2551
5	Frank Schrader	Germany	19 mins 50 secs	2353
6	Ludovic Szewczyk	Belgium	18 mins 35 secs	2328
7	Siim Kask	Estonia	18 mins 53 secs	2322
8	james su	Canada	16 mins 28 secs	2321
9	kowido	No Country Set	19 mins 49 secs	2254
10	Mike Pargeter	United Kingdom	16 mins 41 secs	2216
11	Sean Stuber	United States	20 mins 00 secs	2150
12	Randy Gettman	United States	18 mins 58 secs	2146
13	Filipe Silva	Portugal	18 mins 04 secs	2139
14	owbeg	Ukraine	15 mins 48 secs	2109
15	Dalibor Kova	Croatia	18 mins 37 secs	2053
16	Jeff Kemp	Australia	11 mins 38 secs	1942
17	macabre	Russia	19 mins 41 secs	1931
18	Theo Asma	Netherlands	14 mins 48 secs	1829
19	Mojibul Hoque	Bangladesh	03 mins 57 secs	1821
20	Sayan Malakshinov	Russia	04 mins 32 secs	1809
21	Kevan Gelling	Isle of Man	18 mins 28 secs	1781
22	Anna Onishchuk	Ireland	17 mins 12 secs	1706
23	DikkieDick	Netherlands	13 mins 58 secs	1696
24	Alexander Polivany	Ukraine	20 mins 00 secs	1675
25	mentzel.iudith	Israel	20 mins 00 secs	1575
26	Marc Thompson	Australia	19 mins 39 secs	1557
27	Q	Belgium	16 mins 12 secs	1551
28	clee	United States	17 mins 56 secs	1441
29	NickL	United Kingdom	19 mins 55 secs	1402
30	Pavel Zeman	Czech Republic	20 mins 01 secs	1375
31	Gerry171	United States	19 mins 00 secs	1345
32	Rajesh Venkataramani	India	16 mins 43 secs	1341
33	Rosemary	United States	18 mins 38 secs	727
34	Jerry Bull	United States	18 mins 47 secs	40

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.