08 September 2011

SAVE EXCEPTIONS and LOG ERRORS: objections raised (7253)

The 7 September quiz asked you to compare and contrast the use of SAVE EXCEPTIONS and LOG ERRORS, two mechanisms for continuing past errors raised in DML statements.

Three players wrote with objections, two concerning this choice, which was scored as correct:

When you use SAVE EXCEPTION with FORALL statement, if any of the DML statements fail, changes made to rows by those DML statements are rolled back. When you use LOG ERRORS with your DML statement and that statement fails, Oracle will not necessarily roll back all changes to rows modified by that statement.

1. "I think that this choice is scored wrongly. If we use FORALL with SAVE EXCEPTIONS, the changes performed by the previous DML-s ( the previous successful iterations of the same FORALL statement ) ARE NOT rolled back."

2.  "I suppose, that when you use "LOG ERRORS" clause, words "statement fails" mean only one thing: that number of invalid records have exceeded LIMIT value, and statement immediately stopped with exception. In that case, as I know, Oracle will necessarily roll back all changes to rows modified by that statement. In other hand, when number of erroneous records has not reached LIMIT value, your statement does not fail, it finished successfully."

My response to #1: I don't see this point. We are saying in the choice that the changes made by any failed statement are rolled back. We don't even talk about successful statements - and you are right, those are not rolled back.

My response to #2: ARGH! I think you make a very good point; we were a bit sloppy with words here. The DML statement as a whole does not fail, just because an attempt to change a row identified by that statement fails. The statement, in fact, continues to execute.

One player also wrote regarding this choice:

You can use FORALL SAVE EXCEPTIONS with dynamic SQL, but LOG ERRORS may only be used with static DML statements.

He said: "In the answer 4 example, FORALL is NOT part of the dynamic DML. So I doubt the question can be answered in a clear way given this example. I guess my objection will be overruled by pointing out the choice of the phrase "used with dynamic DML". Well, my grandmother could cook a great meal WITH dynamic DML, if necessary."

My response: I don't really get it. You are right; FORALL is not part of either the static or dynamic DML statement that is "attached" to the FORALL header. But my sentence doesn't claim that it is, does it?

It simply asserts that you can do this

FORALL ...
SAVE EXCEPTIONS
EXECUTE IMMEDIATE my_dynamic_dml;

That is, I can "use" FORALL SAVE EXCEPTIONS to execute a dynamic SQL statement.


I am interested to hear your comments; at this moment, though, I am inclined to issue a re-scoring on choice #483, and correct the verbiage to the following (change in bold):

When you use SAVE EXCEPTION with FORALL statement, if any of the DML statements fail, changes made to rows by those DML statements are rolled back. When you use LOG ERRORS with your DML statement and an attempt to update a row fails, Oracle will not necessarily roll back all changes to rows modified by that statement.

4 comments:

  1. Hello Steven, All,
    ( just a correction to the previous post ).

    Each and every DML STATEMENT, when it fails, it ALWAYS rolls back its own changes, and this IS TRUE since the very first days of Oracle.
    ( In a very old version, maybe Oracle 4 or 5, I even remember that a failed statement
    caused a rollback of the entire transaction, but that's history).

    Anyway, the temptation to compare SAVE EXCEPTIONS to LOG ERRORS is natural from the point of view that each one allows a form of continuing past errors, but any comparison in fact stops here.

    Allows to continue WHAT ?

    1.
    SAVE EXCEPTIONS allows to continue the FORALL statement, which is a PL/SQL statement and NOT an SQL statement.
    Each and every DML iteration in FORALL is in fact a single SQL statement, which behaves just like any other DML statement, with or without the SAVE EXCEPTIONS, that is, if it fails, it rolls back its own changes.

    FORALL is in fact a "group of several DML statements" and what SAVE EXCEPTIONS does
    is just to allow this "group of statements" to continue processing when one group member
    ( one single DML statement ) failed.

    2.
    LOG ERRORS does not change this "eternal truth", if its (single) DML statement fails,
    then its own changes are rolled back, just like in a single DML iteration of a FORALL.

    What LOG ERRORS does is that it prevents the statement to fail immediately when a change to
    a row produces an error, and, as long as this error can be logged (not all types of errors allow this), the statement processing continues, maybe to a successful completion of the statement as a whole.

    So, the real difference is that SAVE EXCEPTION manages the behavior of a programming construct
    ( the FORALL statement ) that groups SEVERAL DML STATEMENTS, inside the PL/SQL layer,
    while LOG ERRORS manages the internal processing of a SINGLE DML STATEMENT, inside the SQL layer.

    By the way, the DML statement inside a FORALL loop used to even be able to contain a LOG ERRORS of its own, and this worked until 10g.
    In 11gR1 this produces the following error:
    ORA-38909: DML Error logging is not supported with BATCH ERROR mode

    It is not clear to me why was this restriction introduced, because the two mechanisms
    are essentially different and act at different layers.
    ( this was the subject of the quiz from July 8, 2010 ).

    But the outcome of a single DML, with or without a LOG ERRORS, is just the same as the outcome of any other DML, including any DML used in a FORALL loop, with or without SAVE EXCEPTIONS:

    A single failed DML statement will ALWAYS ROLL BACK its own changes.

    In what regards the ROLLBACK logic, there is NO DIFFERENCE between a single DML iteration
    in a FORALL loop or a single DML outside a FORALL loop, with or without a LOG ERRORS clause.

    So, this choice should be rescored and, also changed in its presentation for the Quiz archive.

    I think I am right to suppose that all the players who did not mark this choice as correct based their decision on the same argument, which is in fact the correct decision.

    The question should have been presented completely differently for being suitable for the point presented in the answer, namely the "granularity" issue.

    Thanks & Best Regards,
    Iudith

    ReplyDelete
  2. Hello Steven,

    Thanks a lot for having made the corrections to this quiz, that probably all of us will remember
    for a long time :)

    Just for closing this issue and making the archived answer of choice 483 be strictly correct, though, there still are some changes required:

    1.
    Regarding the FORALL:

    The following statement:

    "It's "all or nothing" for each statement executed by FORALL with SAVE EXCEPTIONS."

    is "overloaded", the SAVE EXCEPTIONS being superfluous here, that is, the correct one is:

    "It's "all or nothing" for each statement executed by FORALL",

    maybe with the addition that SAVE EXCEPTIONS ensures that ALL the iterations will be executed, each of them being either successful or not.


    2.
    Regarding the LOG ERRORS:

    The following statement

    "With LOG ERRORS, the DML statement never fails."

    added in the comments at the beginning of the quiz is not correct.

    Using LOG ERRORS still DOES NOT ensure that the DML statement will always succeed, it only allows for a possibility for this to happen,
    instead of having it fail immediately on the first error caused by a row change.

    The statement can still fail (and rollback ALL its changes) if any of the following is true:

    a) the REJECT LIMIT is exceeded
    b) a row causes a type of error that cannot be trapped and logged by the DML error logging mechanism.

    So, the correct statement would be:

    "With LOG ERRORS, the DML statement does not necessarily fail if a change of an individual row causes an error".


    Thanks once again and, as it was already said with other occasions on this blog,
    is there anyone that did not learn from this quiz ?

    ( this, of course, not meaning that this is exactly our preferred way to learn things :) :) :) )

    Best Regards,
    Iudith Mentzel

    ReplyDelete
  3. HAI CAN ANY EXPLAIN with simple points............?what is save_exceptions?when it is used?what is the use of this save_exception?is this only use in bulk_collect?in cursors is it not possible to use save_exceptions....then?how to use save_exception in a plsql program on which condition where ..step by step?

    ReplyDelete
  4. Sumanth, Unfortunately, I do not have the time, and this is probably not the best place, to answer your general questions. Can you get hold of my Oracle PL/SQL Programming book? Available on Safari as an ebook: http://shop.oreilly.com/product/0636920024859.do. That will answer a lot of your questions.

    ReplyDelete