The PL/SQL Challenge (www.plsqlchallenge.com) offers a daily quiz on the PL/SQL language, through which thousands of Oracle technologists demonstrate and deepen their knowledge of PL/SQL. This blog contains posts by the PL/SQL Challenge founder, Steven Feuerstein, as well as comments from players.
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?
Subscribe to:
Post Comments (Atom)
Hi Steven,
ReplyDeleteabout the "default" packages, I was irritated by this question as well. Especially as a non native speaker I translated "default" with packages which are installed out-of-the-box and not how the PL/SQL compiler will resolve names. So I thought it's some kind of "trick" question, because there are way more than 2 packages installed out-of-the-box.
I have a different concern about
If you don't provide an error code in the parameter list of SQLERRM, it will use SQLCODE as the default.
I think that is not 100% correct that it is using SQLCODE. As you can also see in the STANDARD package, it doesn't default the parameter with SQLCODE, it has two implementations for this interface.
The parameter less interface will use the current error stack and not just SQLCODE. This can be proved with the following test case which has two errors on the stack and which are returned by a call to SQLERRM. But SQLCODE will just return -1422 for the "too many rows" and not the 100 of the "no data found" but which also shows up in the result of SQLERRM.
BEGIN
DBMS_OUTPUT.put_line ('sqlerrm with too_many_rows code specified: '||SQLERRM(-1422));
begin
raise no_data_found;
exception when others then
raise too_many_rows;
end;
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.put_line ('sqlcode: '||sqlcode||' sqlerrm without sqlcode: '||SQLERRM);
END;
Regards
Patrick
Hi Steven,
ReplyDeleteAs for the "a" part, I'm voting for a re-scoring. I was not aware of the concept of default packages (versus built-in), but then again there are a lot more things in Oracle that I'm not aware of (yet).
I had in mind that default versus non-default would be something like dbms_output versus utl_mail.
Not that this one question is such a big deal of course, in the light of our journey to supreme PL/SQL wisdom.
Kind regards,
Ward
I've never heard of STANDARD and DBMS_STANDARD referred to as "DEFAULT" either...
ReplyDeleteI selected that option is cos the focus was on SQLERRM, which is defined in STANDARD, and decided to take the extra info as an FYI..
But ya, I do agree with you that leaving out the extra info would be good...
And if Oracle docs don't refer to them as "DEFAULT", defly a good idea to consider re-scoring, even tho i got it right...which was a moment of intuition/fluke, however you want to call it :)
I agree that a rule for answer/question should be "Do not include extraneous information"
ReplyDeleteI'm sure that some people confused "default" with build-in and I can understand that. I did not but I agree with a re-scoring (i.e put that answer as checked to everyone that played).
Patrick is right, but even the oracle documentation (http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/sqlerrm_function.htm) says "The function SQLERRM returns the error message associated with its error-number argument. If the argument is omitted, it returns the error message associated with the current value of SQLCODE" )
So the answer is correct by the documentation, but wrong in pratice due that it uses really the chain of SQLCODEs raised.
Interesting how people have different takes on this.
ReplyDeleteWhether or not the package is described as default/built-in or otherwise, the question remains the same, as does the answer.
I would vote to keep the scoring as-is.
Cheers, P
Regarding part "a", I agree with other posters that extraneous info is probably not a good idea, especially if turns out to be wrong or ambiguous. For myself, though, I have found the extra bits to be interesting and appropriate in the spirit of encouraging enhanced learning about the language. But since there's money on the line, maybe that info could just be included in the next-day quiz answer review.
ReplyDeleteI don't think re-scoring is necessary. The question is about SQLERRM, which the first part of the quiz question addressed correctly ("This function is defined in the STANDARD package,..."). The question is not about the STANDARD package, which the second part addresses ("...one of two default packages in the PL/SQL language.").
So the answer should be based on the information about the subject of the question (SQLERRM), not the extraneous information (default packages).
If the extra information had been intentionally wrong to set up a trick question with a "false" answer, I would protest that. I'm personally finding enough challenge with the subject matter alone.
I found a reference to the standard package as being two "default packages", but it seems to be a once of reference. True but guessable.
ReplyDeleteI agree with Patrick, as my interpretation of the first answer regarding SQLCODE as default. I took this quite literally and ultimately looked for evidence in the STANDARD package, but it was not outwardly obvious. It's rather a default that is used internally - is that correct? True but guessable.
SQERRM outside an exception handler - easily demonstrated. True.
Truncation? I guessed perhaps there is a certain scenario "just right", but easy to ascertain from documentation? True but guessable.
I'm not sure if there's need for score adjustment - everyone really on the same page; but based on my score, I'm surprised the average is so high - compared to history I've noticed/remembered in my fallible brain.
The literature external to Oracle Corporation frequently refers to STANDARD and DBMS_STANDARD as the "default" packages; however, such references do not appear in Oracle's official documentation. I found a document within Oracle Education that makes this reference, but it was not product documentation. The Oracle 2 Day Developer's Guide 11g Release 1 refers the reader to the Oracle Database PL/SQL Packages and Types Reference for information about "default packages available with Oracle database."
ReplyDeleteDespite the aforementioned variation in meaning of the phrase "default package," the sentence "This function is defined in the STANDARD package, one of two default packages in the PL/SQL language" (emphasis mine) is not ambiguous since the emphasized text establishes the context as the PL/SQL language and not the database in general. Knowledgeable PL/SQL developers should be aware that unqualified references that cannot be resolved locally will be resolved by default against the STANDARD and DBMS_STANDARD packages. The additional detail may be extraneous, but it is neither incorrect nor misleading.
Although extraneous detail might adversely affect one’s response times (which is reason enough to avoid including unnecessary information), it should not result in an incorrect response. If an extraneous detail exposes a gap in one’s knowledge of the general subject (in this case the PL/SQL language) then one should expect to receive a lower score.
The additional detail did not make the question unfair: rescoring would be inappropriate.
I have seen questions that were more confusing and got not rescore. It is very hard to define a question so that everyone will totally agree on the meaning.
ReplyDeleteI had totally no problem with this question at all and to me it was immediately obvious that this was extra information and that they referred to STANDARD and DBMS_STANDARD as the other DEFAULT package.
I think for a question to be tricky, the number two would have been too easy of a trap. If one confuses DEFAULT with BUILT IN, it would be way too obvious that there are much more than two built-in packages, so this could hardly be called tricky.
So, I totally agree with John above: a rescoring would be inappropriate.
I didn't have a problem with understanding the context in which "default" was used. I've heard several DBAs refer standard/dbms_standard as default/built-in packages, and as jhall62 stated, it is also referenced in various sources.
ReplyDeleteIt seems to me that this particular answer tested the knowledge of what was defined in the standard package. If you knew it was defined in standard then the word "default" shouldn't have impacted the answer.
After all the comments above, I feel comfortable with not re-scoring based on the reference to "default package."
ReplyDeleteRegarding the issue of whether or not SQLCODE is a "default" or SQLERRM, Patrick argues that:
But the choice I offered was:
"If you don't provide an error code in the parameter list of SQLERRM, it will use SQLCODE as the default."
This choice would not be an accurate description of the STANDARD specification's definitions of SQLERRM. In fact, SQLCODE is not specified as a default value; there is an overloading of SQLERRM that has no parameters. Yet, this choice does correctly describe the behavior of SQLERRM based on its usage in a program, which is what I am describing in this choice ("if you don't provide..."). So I feel that this is valid.
Finally, Patrick states that "The parameter less interface will use the current error stack and not just SQLCODE. This can be proved with the following test case which has two errors on the stack and which are returned by a call to SQLERRM. But SQLCODE will just return -1422 for the "too many rows" and not the 100 of the "no data found" but which also shows up in the result of SQLERRM."
Patrick seems to be arguing that if I pass an argument of SQLCODE to SQLERRM, I will get a different result than if I pass no argument to this function (if this is not your point, Patrick, I am not sure how it would apply to this question). When I ran a test comparing those two ways of calling I got the same result:
BEGIN
BEGIN
RAISE NO_DATA_FOUND;
EXCEPTION WHEN OTHERS THEN RAISE TOO_MANY_ROWS;
END;
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.put_line ('sqlcode: ' || SQLCODE);
DBMS_OUTPUT.put_line (' sqlerrm without argument: ' || SQLERRM);
DBMS_OUTPUT.
put_line (
' sqlerrm with sqlcode passed explicitly: ' || SQLERRM (SQLCODE));
END;
sqlerrm without argument: ORA-01422: exact fetch returns more than requested number of rows
ORA-01403: no data found
sqlerrm with sqlcode passed explicitly: ORA-01422: exact fetch returns more than requested number of rows
ORA-01403: no data found
They both return the same string. Patrick, have I missed your point?
So my conclusion is: no re-scoring or change in ranking; avoid extraneous information in the future; validate terminology used in the quizzes with Oracle documentation (or define them within the quiz context).
My apologies; I left out a sentence in the previous post. Namely:
ReplyDeleteRegarding the issue of whether or not SQLCODE is a "default" or SQLERRM, Patrick argues that:
"I think that is not 100% correct that it is using SQLCODE. As you can also see in the STANDARD package, it doesn't default the parameter with SQLCODE, it has two implementations for this interface."
I hesitated about the answer that implied that SQLERRM returns the same as SQLERRM(SQLCODE); the result will only be *exactly* the same when called from within the exception handler, because some error messages include extra information that is supplied at runtime. For example:
ReplyDeletecreate table t (c number, constraint myconstraint check (c=1));
declare
sqc number;
begin
begin
insert into t values (0);
exception
when others then
dbms_output.put_line('Inner: SQLERRM(SQLCODE) = ' || SQLERRM(SQLCODE));
dbms_output.put_line('Inner: SQLERRM = ' || SQLERRM);
sqc := SQLCODE;
end;
dbms_output.put_line('Outer: SQLERRM(' || sqc || ') = ' || SQLERRM(sqc));
end;
Inner: SQLERRM(SQLCODE) = ORA-02290: check constraint (CRS.MYCONSTRAINT) violated
Inner: SQLERRM = ORA-02290: check constraint (CRS.MYCONSTRAINT) violated
Outer: SQLERRM(-2290) = ORA-02290: check constraint (.) violated
Notice that once we're outside the block, calling SQLERRM with the error code doesn't return the same error message.
I feel we are driving away from the original question/answers here:
ReplyDeleteIt was not said that SQLERRM returns the same as SQLERRM(SQLCODE) always. Dependent on the state of the error stack and the place where you call either, they may return a different string, as Patrick shows in his code sample. But that changes nothing about that SQLERRM without arguments takes the current value of SQLCODE as its lookup argument, like Steven showed in his sample, where he called both types in the same place (the Exception handler).
I may be missing something as well, but to me, re-reading the question and answers, it still feels there is nothing wrong with the question and the correctness of the answers.
Toine