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
Questions raised by players regarding 8 July quiz (LOG ERRORS and SAVE EXCEPTIONS)(464)
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
Subscribe to:
Post Comments (Atom)
The following is my original email:
ReplyDeleteThe 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.
Below is the script that I included:
CREATE TABLE test_table (test_id INTEGER PRIMARY KEY)
ORGANIZATION INDEX;
EXECUTE sys.DBMS_ERRLOG.create_error_log('TEST_TABLE','TEST_ERRLOG');
CREATE OR REPLACE PROCEDURE test_proc IS
TYPE test_values_t IS TABLE OF test_table%ROWTYPE
INDEX BY BINARY_INTEGER;
test_values test_values_t;
BEGIN
test_values(1).test_id := 1;
FORALL i IN 1 .. test_values.COUNT
SAVE EXCEPTIONS
INSERT INTO test_table
VALUES test_values(i)
LOG ERRORS INTO test_errlog REJECT LIMIT UNLIMITED;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE;
END test_proc;
/
EXECUTE test_proc;
Fascinating! By far the best part of the PL/SQL Challenge for me is when I learn something new about PL/SQL from a player. I was not even aware of this new error in Oracle11g.
ReplyDeleteHaving said that, I am a bit puzzled. When I run John's script I see the error. When I run the code below, I do not get an error. Can anybody see why that might be?
DROP TABLE parts
/
CREATE TABLE parts
(
id NUMBER
, name VARCHAR2 (10)
)
/
BEGIN
INSERT INTO parts VALUES (1, 'Gearbox');
INSERT INTO parts VALUES (2, 'Box');
COMMIT;
END;
/
DROP TABLE err$_parts
/
BEGIN
DBMS_ERRLOG.create_error_log (dml_table_name => 'PARTS');
END;
/
CREATE OR REPLACE PROCEDURE test_se_le
IS
TYPE filters_t IS TABLE OF parts.name%TYPE;
l_filters filters_t := filters_t ('G%', 'B%');
BEGIN
FORALL indx IN 1 .. l_filters.COUNT
SAVE EXCEPTIONS
UPDATE parts
SET name = name || name
WHERE name LIKE l_filters (indx)
LOG ERRORS INTO err$_parts REJECT LIMIT UNLIMITED;
END;
/
BEGIN
test_se_le;
END;
/
SELECT * FROM err$_parts
/
Actually the part I had trouble with was "the SQL%BULK_EXCEPTIONS pseudo-collection will always be empty after executing that statement".
ReplyDeleteAn update that raises a unique key violation doesn't trigger DML error logging. So in this case, you can get errors in SQL%BULK_EXCEPTIONS
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_9014.htm#BGBEIACB
Example tested in 10.2.0.4 EE and XE:
clear screen
drop table test_errlog purge;
drop table test_table purge;
CREATE TABLE test_table (test_id INTEGER primary key, test_val integer unique);
EXECUTE sys.DBMS_ERRLOG.create_error_log('TEST_TABLE','TEST_ERRLOG');
insert into test_table select level, level from dual connect by level < 6;
commit;
CREATE OR REPLACE PROCEDURE test_proc IS
TYPE test_values_t IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
test_values test_values_t;
BEGIN
test_values(1) := 1;
test_values(2) := 2;
test_values(3) := 3;
test_values(4) := 4;
--
FORALL i IN 1 .. test_values.COUNT
SAVE EXCEPTIONS
UPDATE test_table
SET test_val = CASE WHEN test_id in (1,2) THEN 10 ELSE 20 END
WHERE test_id = test_values(i)
LOG ERRORS INTO test_errlog REJECT LIMIT UNLIMITED;
dbms_output.put_line('A:'||sql%bulk_exceptions.count);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('B:'||sql%bulk_exceptions.count);
FOR i IN 1..sql%bulk_exceptions.count LOOP
dbms_output.put_line('Err:'||SQL%BULK_EXCEPTIONS(i).ERROR_index||'-'||SQL%BULK_EXCEPTIONS(i).ERROR_CODE);
END LOOP;
FOR c_rec in (SELECT test_id, test_val from TEST_TABLE) loop
dbms_output.put_line(c_rec.test_id||'-'||c_rec.test_val);
END LOOP;
ROLLBACK;
RAISE;
END test_proc;
/
show errors procedure test_proc
EXECUTE test_proc;
rollback;
Hi
ReplyDeleteI also made a
post in my blog about that issue in my blog.
The exception doesn't occurs in 11g with update or delete.
I didn't test with merge but I think it will occur.
I'm almost sure that the behavior of combining "SAVE EXCEPTIONS" and "LOG ERRORS" within a FORALL statement is not undefined for Oracle 10g (like for instance the triggers).
BTW my testing I used a trigger (insert, delete, update) in the table that raised an exception so I could test all the distinct DML (insert, delete, update)
Just like John, I was asked by Steven to reply to this post.
ReplyDeleteAs 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 layer
ReplyDeleteevaluates 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 the
ORA-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 !
Thanks & Best Regards,
Iudith Mentzel
I am in agreement with Gary, I also sent the same scenario as he mentioned. The correct choice as per Steve was:
ReplyDelete"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.
Regards,
Anuj Parashar
I am in agreement with Gary, I also sent the same scenario as he mentioned. The correct choice as per Steve was:
ReplyDelete"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.
Regards,
Anuj Parashar
Sorry about the delay in responding. I've been busy getting ready for the playoff.
ReplyDeleteSo....if the scenario I described in my question is not "always" right, then that choice is wrong.
I will issue a correction and adjust scores and ranks....very soon!
This is also a great reinforcement for my new checklist for quizzes - that use of the word "always" should have been and now mos definitely IS a red flag for any future quizzes.
Thanks for all the great analysis and learning experience!
Steven
I too had raised the same question in my blog @ http://doubtregarding8thjulyquestion.blogspot.com/ on 8th jul.
ReplyDeletethank god i got more info here than i expected :-)