I received this question in my In Box over the weekend:

Hi Steven,

I have grown up reading the PL/SQL bible, Oracle PL/SQL Programming. I have the 5th edition with me right now, Indian reprint, ISBN - 971-81-8404-949-7.

I was going through the chapter 21 : Optimizing PL/SQL performance. Please refer to the section 'ROLLBACK behaviour with FORALL'.  In point number 2, it's mentioned that "Any previous DML operations in that FORALL statement that already completed without any error are NOT rolled back."

Can you please explain this ? As far as I understand a FORALL can have only one DML statement, so all the previous work done by the DML will be rolled back.

Thanks for your time. Hoping a response soon.

An Indian reprint of the 5th edition? You are so lucky. I don't even have one of those. I don't even have a copy of that! I will have to ask my friends at O'Reilly Media for a copy! :-) 

Now to answer your question, and it is a common one:

Perhaps the way I phrased this is not as clear as could be. So let's take it step by step and hopefully all will become clear. Let's start with a simplified FORALL statement:

FORALL iterator IN low_value .. high_value

FORALL is itself a PL/SQL statement, and within it you find a single DML statement - and no more than one, as you so correctly point out.

When the PL/SQL engine encounters a FORALL statement, it uses the IN clause to determine how many individual DML statements will be generated (with bind variables drawn from the bind array(s)) and then passed to the SQL engine. 

In the sentence you quoted above, I use the term "DML operations" to refer to each of these generated  statements.

So, for example, in this little example, a total of 5 DML statements might be executed by the SQL engine. 

   l_department_ids ids_t := ids_t (1, 2, 3, 4, 5);
   FORALL indx IN low_value .. high_value
      UPDATE employees SET salary = salary * 2
        WHERE employee_id = l_department_ids (indx);
      dbms_output.put_line (SQL%ROWCOUNT);

Why only "might be"? Suppose that the salary column is defined as NUMBER(3) and the salary for an employee in department 2 is 600. When I double that, I now need 4 digits to store the value. So the SQL engine will reject the update and throw an error back to the PL/SQL engine.

So in that case, the following takes place (or not as the case may be):
  • one DML statement completed (for employee ID 1)
  • one failed (for ID 2)
  • three of them (for IDs 3-5) never even executed
And the PL/SQL block in which the FORALL was executed traps the error from the second statement executed and displays 1 because it reflects the total number of rows modified by the FORALL statement, not the last individual DML statement generated by the FORALL statement.

And this tells us quite unambiguously that the effect of the first UPDATE is not rolled back by the second UPDATE's failure.  

Oh and by the way: this behavior has nothing whatsoever to do with FORALL. You could execute three separate DML statements in a block and it works the same: if the first two finish successfully and the third fails, the effects of those two statements are not rolled back.

Of course, if your exception propagates all the way out of the top-most PL/SQL block unhanded, then all the outstanding changes in your session will be rolled back.

Hope that helps! Comments, questions, anyone?


  1. Steven,
    Thanks for the prompt explanation. It's crystal clear now.
    I got confused because I did not assume that the exception is getting caught. May be because exceptions were covered later in the chapter.

    About your books, what can I say, they are bread and butter !

  2. PL/SQL Programming: indeed the bible! Taught myself PL/SQL 10 years ago on the 3rd Edition and been keeping the latest version on my library shelves for reference ever since!
    A great accompaniment to Forall is the 'Save Exceptions' clause which has two great advantages: (1) it does not stop DML statements subsequent the statement that caused the exception from being executed; (2) it allows one to capture each individual DML error and decide how to handle it. I sometimes 'abuse it' a little bit when I want to make bulk inserts/updates that I know *may* cause errors. For example in a multi-user environment where the possibility exists that a parent record may be deleted by another user, but this is a valid business scenario in which case the individual insert/update can be safely ignored. So, with 'Save Exceptions', I can analyse each DML error, and if it's in the list of 'acceptable errors' I can just ignore it and commit the rest. A bit of a hack, but the alternative (iteratively issuing individual DMLs and watching paint dry) is unacceptably slow. We've noticed performance improvement of at least one order of magnitude (sometimes several orders of magnitude) with Bulk DMLs!

  3. Hi,
    Question on your example with forall and 5 DML.
    First 2 are inserted, the 3rd has an issue.
    Could I stop the insert and also rollback the first 2, in order to re-trigger all the DML process?

    Thanks !

    1. Sure, just put a savepoint before you get started, trap the exception, issue a rollback to that savepoint, and start again.

  4. How about if we have a big amount of inserts and at the first exception we want to stop the process , rollback the inserted ones and end the procedure?

    The savepoint will throw me back into the code and rollback the changes, then start again. But what if I want to stop the procedure and rollback the changes?

    And about the engines? Let say we have 100 DMLs that will be transferred from PL/SQL engine to SQL engine. The first 8 are ok, the 9th has an error. The process should rollback the first 8 and not continue with the rest.
    Does the SQL engine will stop at the 9th? Or it will run bulk all the 100 DMLs due to bulk functionality of FORALL? Does the below code stop at the 9th line and rollback the first 8?

    END procedurename;

    Thank you.

    1. If you want to stop with the first exception (whether it be the first statement or 9th), do NOT include SAVE EXCEPTIONs. Any statements that succeeded up to the point of failure are NOT automatically rolled back. So trap the exception with WHEN OTHERS, then rollback.

      Or let the exception propagate out unhandled and a rollback will be performed for you.

      Does that answer your question?


Post a Comment

Popular posts from this blog

Table Functions, Part 1: Introduction and Exploration

Get rid of mutating table trigger errors with the compound trigger

Quick Guide to User-Defined Types in Oracle PL/SQL