Skip to main content

FORALL and ROLLBACKs


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
   dml_statement

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. 

DECLARE
   TYPE ids_t IS TABLE OF INTEGER;
   l_department_ids ids_t := ids_t (1, 2, 3, 4, 5);
BEGIN
   FORALL indx IN low_value .. high_value
      UPDATE employees SET salary = salary * 2
        WHERE employee_id = l_department_ids (indx);
EXCEPTION
   WHEN OTHERS THEN
      dbms_output.put_line (SQL%ROWCOUNT);
      RAISE;
END;

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?

Labels:

Comments

  1. UnknownMay 13, 2014 at 1:23 AM

    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!

    ReplyDelete
  2. AnonymousDecember 6, 2017 at 2:24 AM

    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 !

    ReplyDelete
    Replies
    1. Steven FeuersteinDecember 6, 2017 at 3:06 AM

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

      Delete
  3. AnonymousDecember 11, 2017 at 5:38 AM

    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?

    FORALL i IN INDICES OF clc_in_param SAVE EXPECTIONS
    INSERT......
    EXCEPTION WHEN prgm_ex THEN ROLLBACK;
    END procedurename;

    Thank you.

    ReplyDelete
    Replies
    1. Steven FeuersteinDecember 11, 2017 at 5:53 AM

      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?

      Delete
  4. AnonymousDecember 11, 2017 at 6:22 AM

    Yes, thank you

    ReplyDelete
  5. AnonymousApril 23, 2019 at 12:13 PM

    1077/5000

    Hi Steven,

    I've been searching the internet for an example that can help me with the following case: I want to apply FORALL to execute inserts in 3 different tables, so I have to write 3 FORALL. These inserts are dependent on each other, that is, if a record is not inserted in table 1, the corresponding record must not be inserted in table 2, nor in table 3, and vice versa, if the insertion in table 3 fails, I I would like to do ROLLBACK of what was executed in table 1 and 2. This is very easy to control with insertions one by one (not massive), using SAVEPOINT, however using FORALL it has been a little complicated to control it, since when finishing a FORALL and enter the next FORALL, the instructions of the first have already been executed. I have tried to control it using SAVE EXCEPTIONS but it has been a little uphill. If you have any recommendation or idea that I can use, I would greatly appreciate it, since I must make insertions of more than 20 million records. Sorry for my English. Greetings.

    Erika.

    ReplyDelete
    Replies
    1. MuniMay 28, 2019 at 8:44 AM

      You can delete (or use a flag to identify which ones you do not want to insert) the rows in exception from the collection for the FORALL before proceeding to the next FORALL.

      Delete
  6. Steven FeuersteinJune 4, 2019 at 3:14 AM

    Wow. Sometimes I hate blogger. Just wrote a long reply and it was swallowed up, disappeared.

    My apologies for the long delay in responding. I don't know how I missed this.

    The bottom line is that this is indeed a scenario in which row-by-row is easier to manage than bulk.

    You need to find a way to use the information in SQL%BULK_EXCEPTIONS to identify the rows in the 2nd and 3rd FORALL which should not be inserted.

    The specific logic to make that identification is very app-specific. But once you have that, you either delete the not-to-be-inserted elements from the bind arrays used in the other FORALLs - or you build an inclusion array (those elements that are STILL to be inserted) to be used with INDICES OF or VALUES OF.

    If you are still struggling with this issue, don't hesitate to reach out directly via email: Steven dot Feuerstein at oracle dot com.

    Best would be to upload your code to an unlisted script on LiveSQL.oracle.com, so I can check it out and maybe even run it.

    ReplyDelete

Post a Comment

Popular posts from this blog

Running out of PGA memory with MULTISET ops? Watch out for DISTINCT!

A PL/SQL team inside Oracle made excellent use of nested tables and MULTISET operators in SQL, blending data in tables with procedurally-generated datasets (nested tables).  All was going well when they hit the dreaded: ORA-04030: out of process memory when trying to allocate 2032 bytes  They asked for my help.  The error occurred on this SELECT: SELECT  *    FROM header_tab trx    WHERE (generated_ntab1 SUBMULTISET OF trx.column_ntab)       AND ((trx.column_ntab MULTISET             EXCEPT DISTINCT generated_ntab2) IS EMPTY) The problem is clearly related to the use of those nested tables. Now, there was clearly sufficient PGA for the nested tables themselves. So the problem was in executing the MULTISET-related functionality. We talked for a bit about dropping the use of nested tables and instead doing everything in SQL, to avoid the PGA error. That would, however require lots of wo...
4 comments
Read more

How to Pick the Limit for BULK COLLECT

This question rolled into my In Box today: In the case of using the LIMIT clause of BULK COLLECT, how do we decide what value to use for the limit? First I give the quick answer, then I provide support for that answer Quick Answer Start with 100. That's the default (and only) setting for cursor FOR loop optimizations. It offers a sweet spot of improved performance over row-by-row and not-too-much PGA memory consumption. Test to see if that's fast enough (likely will be for many cases). If not, try higher values until you reach the performance level you need - and you are not consuming too much PGA memory.  Don't hard-code the limit value: make it a parameter to your subprogram or a constant in a package specification. Don't put anything in the collection you don't need. [from Giulio Dottorini] Remember: each session that runs this code will use that amount of memory. Background When you use BULK COLLECT, you retrieve more than row with each fetch, ...
4 comments
Read more

PL/SQL 101: Three ways to get error message/stack in PL/SQL

The PL/SQL Challenge quiz for 10 September - 16 September 2016 explored the different ways you can obtain the error message / stack in PL/SQL. Note: an error stack is a sequence of multiple error messages that can occur when an exception is propagated and re-raised through several layers of nested blocks. The three ways are: SQLERRM - The original, traditional and (oddly enough) not currently recommended function to get the current error message. Not recommended because the next two options avoid a problem which you are unlikely  to run into: the error stack will be truncated at 512 bytes, and you might lose some error information. DBMS_UTILITY.FORMAT_ERROR_STACK - Returns the error message / stack, and will not truncate your string like SQLERRM will. UTL_CALL_STACK API - Added in Oracle Database 12c, the UTL_CALL_STACK package offers a comprehensive API into the execution call stack, the error stack and the error backtrace.  Note: check out this LiveSQL script if...
5 comments
Read more