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.

11 July 2010

The quiz on 8 July asked this question:
"You can use both LOG ERRORS and SAVE EXCEPTIONS to continue past errors that occur when executing DML statements (inserts, updates, deletes and merges). Which of the following statements describe what happens when you use both in a single FORALL statement (assume that DBMS_ERRLOG.CREATE_ERROR_LOG was used to create the error log table as required)?"
We scored as correct the following choice:
"If, within a FORALL statement, you include (in a syntactically valid fashion) both SAVE EXCEPTIONS and LOG ERRORS with the rejection limit set to UNLIMITED, then the SQL%BULK_EXCEPTIONS pseudo-collection will always be empty after executing that statement."
Two players expressed a concern about this choice. I will invite them to post their comments in reply to this blog. Then I will reply to them!
SF

10 comments:

The behavior of combining "SAVE EXCEPTIONS" and "LOG ERRORS" within a FORALL statement is undefined for Oracle 10g; however, in 11g an ORA-38909 runtime exception is raised. Although one might guess that "LOG ERRORS REJECT LIMIT UNLIMITED" would result in SQL%BULK_EXCEPTIONS always being empty (which is indeed the case for Oracle 10g), it seems unfair to penalize a player for not divining the effects of using statements with undocumented behavior. It is unlikely that one would experiment with the combined use of "SAVE EXCEPTIONS" and "LOG ERRORS" (except in conjunction with the PL/SQL Challenge). Given that Oracle added an exception in 11g to address this issue, it is likely that the ability to use the combination in 10g was an accident of implementation and not intended behavior.

Although I am not happy with this quiz question, I am not advocating a change in scoring.

I strongly encourage you to continue offering questions that explore unusual, but documented, aspects of the PL/SQL language. Please don’t let the PL/SQL Challenge devolve into a trivia contest instead of a competition based on the practical application of intended and documented behavior of the PL/SQL language.

Steven asked me to supply a script to demonstrate the issue which I included with the following email:

The attached script will not raise an exception when run on 10g but will raise an ORA-38909 exception when run on 11g. I encapsulated the "FORALL" in a stored procedure to demonstrate that it compiles successfully and that the exception occurs at runtime. If test_values is empty then no exception is raised.

Had the question been based on using 11g instead of 10g I would have had no issue since the exception is documented in Error Messages. If oracle intended to permit the combined use of "SAVE EXCEPTIONS" and "LOG ERRORS" in 10g I doubt the capability would have been removed in 11g. When researching this I found reference to a message from Oracle at http://aspn.activestate.com/ASPN/Mail/Message/perl-dbi-dev/3837250. While the message indicates that Oracle is aware that the combination works in 10g, the lack of documentation in the Oracle manuals and the subsequent addition of an exception for this in 11g leads me to the conclusion that the capability "just happened" in 10g.

Again I want to thank you for your prompt responses to email. I also appreciate the professional manner in which you have handled the issues that I and others have raised.

As John already explained the situation I mentioned in the email, I'll just say I agree with everything he said (including the part that I'm not asking for a recount).

This really seems like a hidden and not assumed bug, as there is pretty much zero documentation about this error, and it only applies to INSERTs (UPDATEs and DELETEs don't raise the exception, as first pointed out to me by Adriano Teixeira). Also, it's not backward compatible, which means everything that uses this will suddenly stop working after the upgrade.

I actually agree with the only explanation I read coming from someone from Oracle (that this is not a good practice, and there's no reason to use both), but this is really a wicked way to "force" this.

The explanation given in the Quiz answer is perfectly logical in saying that the SQL layerevaluates the SQL outcome of the statement BEFORE the pl/sql layer, which means that if we use REJECT LIMIT UNLIMITED (and to this we should add that we also do not hit any of the cases excepted from the LOG ERRORS capturing as by Oracle's documentation),then we should in fact end with errors logged in the error table and an empty SQL%BULK_EXCEPTIONS pseudo-collection.

Though I did not apply this reasoning, the question looked to me very challenging because I have never thought of using the two features together. Therefore I tried it out before asnwering the quiz, and it happened that I tried it on Oracle11gR1, which caused theORA-38909 error... and I was sure of having chosen the right answer ...

The misleading thing is that the text of this error says:

ORA-38909: DML Error logging is not supported with BATCH ERROR mode

so, not a single word about any of using SAVE EXCEPTIONS (is this what they call "batch mode" ?!?) or using LOG ERRORS or the two of them together.

Since the same test worked ok with SAVE EXCEPTIONS removed, it seemed natural to consider this as the culprit.

If this combination were illegal, then I would have expected a compilation error to be returned and not a run-time error.

Anyway, a very instructive question, from which I learned very much.

I completely agree with Steve's position,the best thing with this challenge is that we can always learn new things, even by doing small tests !

I am in agreement with Gary, I also sent the same scenario as he mentioned. The correct choice as per Steve was:"If, within a FORALL statement, you include (in a syntactically valid fashion) both SAVE EXCEPTIONS and LOG ERRORS with the rejection limit set to UNLIMITED, then the SQL%BULK_EXCEPTIONS pseudo-collection will always be empty after executing that statement."

This is definitely not true "always". But I agree that this question has cleared a lot of concepts and I appreciate Steve's efforts to come up with these kind of tricky ones.

I am in agreement with Gary, I also sent the same scenario as he mentioned. The correct choice as per Steve was:"If, within a FORALL statement, you include (in a syntactically valid fashion) both SAVE EXCEPTIONS and LOG ERRORS with the rejection limit set to UNLIMITED, then the SQL%BULK_EXCEPTIONS pseudo-collection will always be empty after executing that statement."

This is definitely not true "always". But I agree that this question has cleared a lot of concepts and I appreciate Steve's efforts to come up with these kind of tricky ones.