30 December 2011

What is a "Generic" Oracle Error Message? (9614)

The 29 December quiz tested your knowledge of the differences between various ways of raising errors and communicating error messages to users. It was a word-based quiz, as opposed to one that is mostly code so - no surprise - a number of players raised objections to some of the phrasing and scoring.

Let's go through these objections and see how much we can learn about PL/SQL through the process. Player comments are in blue. My response is in purple.

Choice 8990:  Both the PEI and RAE implementations allow you to set the error code to one that is not used by Oracle and is returned by a call to SQLCODE.

A player wrote

1. PEI allows code from -20000...-20999, but not only such codes; 
2. Several codes from -20000...-20999 is used by Oracle, for example: "ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes"
3. Is it unambiguous to say that outside this range all codes are used by Oracle, for example, does "ORA-22567: Message 22567 not found; product=RDBMS; facility=ORA" mean that it's used by Oracle?

My response: you are absolutely right that Oracle does, in a few packages, use some of "our" error codes, such as ORA-20000 and ORA-20000. I've always felt that this was rude behavior on Oracle's part. We only get 1000 error codes with which to work; surely, you could leave all of those to us! So good point, but I don't think it makes this choice wrong in any way. With both those implementations, I can choose to set the error code to one that is not used by Oracle (such as -20704). The choice does not claim that it is impossible for me to choose a code that Oracle also uses. 

As to which codes Oracle "uses" - no, I would say that at least for now, -22567 is not in use. But it is certainly the case that Oracle could at some point use these error codes - and we cannot.

Choice 8989: PEI and VE offer "generic" Oracle error messages, while RAE provides an application-specific error message.

Two players raised questions about this choice, and both circle back to the use of the word "generic". My intention behind the use of this word, combined with the "Oracle error message" phrase, is that these are the error messages returned by Oracle and are the same across all installations of Oracle.

I marked answer 8989 as "Incorrect" because PEI uses an application-specific exception - i.e. it's not a "generic" Oracle error. I didn't realize this answer was about the error *message text* in particular. Seems like this answer was a bit ambiguous.

and

I disagree that "RAISE VALUE_ERROR" raises a "generic" error. It raises the very specific error associated with ORA-06502.

and

When you use PRAGMA EXCEPTION_INIT to change the error code of a user-defined exception, then the error message returned by SQLERRM or DBMS_UTILITY.FORMAT_ERROR_STACK is not generic, it is blank. So I’d suggest that while VE offers a "generic" Oracle error message and RAE provides an application-specific error message that PEI does neither. 

My response: the text of the choice is clearly about messages. And my point in this choice is that with the EXCEPTION_INIT pragma, you can change the error code associated with a named exception, but you cannot change the error message. As for VALUE_ERROR resulting in a specific rather than generic error message, I could understand this objection if I wrote the choice as follows:

PEI and VE offer a single "generic" Oracle error message, while RAE provides an application-specific error message.

That is, if I said or implied there was just one "generic" message. But I use the plural form, so I feel it is clear that I am talking about the error messages returned by Oracle, which cannot be changed by the developer with EXCEPTION_INIT or RAISE.

So is a blank message "generic"? If you do not use EXCEPTION_INIT with a user-defined exception, then the error message is, well, generic: "User-defined error". When you assign a different error code to a user-defined exception, the error message is then blank. Gee, I don't know, that seems rather generic to me!

Your thoughts?

28 December 2011

Thanks for a Great Year!

As 2011 comes to a close, I would like to thank the thousands of players who have played the quizzes at the PL/SQL Challenge, especially the daily PL/SQL quiz, and who have also volunteered their time to write quizzes, edit and review quizzes, and provide many ideas for ways to improve the website.

At the PL/SQL Challenge website this year, 4,670 Oracle technologists from 104 countries spent over 32,000 hours submitting 213,077 answers to quizzes. These numbers reflect a very impressive commitment by all these players to improving their skill set in PL/SQL, SQL and APEX.

I am grateful beyond words to our reviewers, listed below with the number of questions they reviewed in parentheses:

Michael Brunstedt (270)
Ken Holmslykke (175)
Elic (172)
Darryl Hurley (129)
Kim Berg Hansen (25)
Patrick Barel (18)
Viji Thatai (6)
Munky (3)

The impact of our reviewers can be seen most clearly in the reduction in errors in our quizzes. In the eight months of play in 2010, we issued corrections for 31 quizzes. In all of 2011, with the addition of SQL and APEX quizzes, we only needed to issue corrections for 20 quizzes. And since January 2011, there have never been more than 2 corrections in a month. That's still too many - and I take full responsibility for all errors! - but it is certainly a big improvement. I expect to see the number decrease further in 2012.

Next, my thanks to the following players who found the time to submit quizzes that were then played in 2011, listed below with the number of questions authored in parentheses:

_Nikotin (8)
Kim Berg Hansen (8)
mentzel.iudith (8)
koko (6)
Scott Wesley (5)
Jeff Kemp (5)
Patrick Barel (5)
Gary Myers (4)
Tim Hall (4)
Christian Rokitta (3)
Ken Holmslykke (3)
Joaquin Gonzalez (2)
anil_jha (2)
Vinod Kumar (2)
Sergey Porokh (2)
Jan Leers (iAdvise.be) (2)
Christopher Beck (2)
Chris Saxon (2)
Keith Hollins (1)
poelger (1)
Radoslav Golian (1)
Ramesh Samane (1)
Marc Thompson (1)
Alexander Polivany (1)
David Codl (1)
Oleg Borodin (1)
Viacheslav Stepanov (1)
Sreeguruparan PA (1)
D.J. Alexander (1)
Darryl Hurley (1)
senthil prakash Muthu Irulappan (1)
Ralf Koelling (1)
Sohilkumar Bhavsar (1)
Neal Hawman (1)
David Alexander (1)
Randy Gettman (1)
Oleg Gorskin (1)
Joni Vandenberghe (1)
voltrik (1)
Sailaja Pasupuleti (1)
Dennis Klemme (1)
Tony Winn (1)

Finally, we have been working on some exciting new features for quite awhile now, and it looks like 2012 is the year in which they will appear, so please be on the lookout for announcements of big changes at the PL/SQL Challenge site soon.

Warmest holiday wishes to everyone!
Steven Feuerstein