08 September 2010
Mistaked reports in 7 September quiz on error handling (1373)
Players reported two problems with the 7 September quiz, which asked "Which of these choices correctly describe the behaviors of the SQLERRM and DBMS_UTILITY.FORMAT_ERROR_STACK functions?" 1. First, we scored the following choice as correct: "When executing SQL statements inside your PL/SQL program, Oracle may raise NO_DATA_FOUND, TOO_MANY_ROWS or DUP_VAL_ON_INDEX. If you handle any of these exceptions in the same block in which the exception was raised, then the string returned by DBMS_UTILITY.FORMAT_ERROR_STACK is the same as that returned by....SQLERRM();" In the answer, I provided a block of code that shows, simply by looking at the output on the screen, that this answer is correct. Well, shows how much I know about PL/SQL (not enough) and how important it is to not rely simply on "eyeballs" to verify results. Jaydeep Cheruku (the first to report this issue) was not content to merely peer at the screen and accept that those two strings were the same. He also checked the length of the strings and discovered that DBMS_UTILITY.FORMAT_ERROR_STACK is one byte longer: it has a carriage returned appended to the end of the error stack string. If only I had written "appears the same" instead of "is the same"! Ah well, that's what I very much enjoy about the PL/SQL Challenge: a chance to learn even more about the PL/SQL language. 2. In another choice (also scored as correct) stated "The SQLERRM function can be used to look up the error message associated with a particular error code, while DBMS_UTILITY.FORMAT_ERROR_STACK will only return information about the currently-raised exception, if any." Eigminas Dagys was the first to question this, by pointing out that if the string returned by the DBMS_UTILITY function contains a stack of errors, it contains information about the all the exceptions raised, not just the "currently-raised exception." Sigh...the pitfalls of language as a communication mechanism. I believe I could argue that this statement is correct; that the error stack returned by DBMS_UTILITY.FORMAT_ERROR_STACK is, in fact, all about the "currently-raised exception." t would be more accurate, however, to say "most-recently raised exception." So I will apply corrections to scoring and make changes to the question text to tighten up the meaning. So Jaydeep and Eigminas each win their choice of an ebook from O'Reilly Media. Thanks for your close, very close, reading of my quizzes and for helping me improve them. Thanks to all the other players who also responded to this quiz, raising the same issues.