22 July 2010
Questions raised regarding 21 July quiz on SQLERRM(1246)
The 21 July quiz asked you to choose which statements about SQLERRM were correct. Several players wrote to me about the following: a. I offer as one of the correct choices: "This function is defined in the STANDARD package, one of two default packages in the PL/SQL language." One person wrote: " I didn't check this answer because I was in the opinion there are a lot more then two default packages (all the dbms_* and utl_* packages)." There is a big difference between a default package and a built-in package. A "default package" means that if you do not have to include the package name in the reference to the element in that package. For example, I can write a WHEN clause like "WHEN NO_DATA_FOUND" and do not have to use "WHEN STANDARD.NO_DATA_FOUND". If Oracle cannot resolve the reference to NO_DATA_FOUND in the block in which it is referenced, Oracle will automatically try to resolve it against one of the two default packages, STANDARD and DBMS_STANDARD. Having said all that, I now realize that Oracle does not seem to use this term to describe STANDARD or DBMS_STANDARD. I also now have another item to add to my checklist on quiz reviews: "Do not include extraneous information." I could have just said that SQLERRM is defined in STANDARD, and left it at that. So I am undecided: should this warrant a re-scoring, in which everyone receives credit for this choice? Or should you chalk this up as a learning experience about PL/SQL? While I chew over this question, I would like to hear what you think. b. Several people believed that you can only call SQLERRM without an argument while inside an exception handler. This is not true. You can call it anywhere, without an argument. Outside of an exception section, it will always show the same message "ORA-0000: normal, successful completion" - precisely because SQLCODE is 0 outside of an exception handler. Other concerns or comments?