This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 31st, when you will be able to use this site as normal.

    Forum Stats

  • 3,890,899 Users
  • 2,269,649 Discussions
  • 7,916,821 Comments

Discussions

FOR Loop Exception Handling

910971
910971 Member Posts: 40
edited May 31, 2012 10:45AM in SQL & PL/SQL
Given the following code:
for x in my_cursor loop
    begin
        <<stmt_1>>
        <<stmt_2>>
    exception
        when others then
            <<log_error>>
    end;
end loop;
Say there are 5 x's in my_cursor. On element x3, stmt_1 works fine but stmt_2 throws an exception. I'd like to rollback ONLY the work done on x3 (in this case, stmt_1), then continue the loop from the next element, x4. Can this be achieved with some combination of savepoint/rollback?

Best Answer

  • JustinCave
    JustinCave Consultant Rochester Hills, MIMember Posts: 30,293 Gold Crown
    In this particular case, I would tend to suggest that you follow the third example I posted where you factor out the core of your loop into a procedure such that you only overwrite the values of a and b if your insert succeeds, i.e.
    CREATE OR REPLACE PROCEDURE proc_name( p_x IN NUMBER, p_a IN OUT NUMBER, p_b IN OUT NUMBER )
    AS
      l_tmp_a NUMBER;
      l_tmp_b NUMBER;
    BEGIN
      SAVEPOINT s1;
      l_tmp_a := p_a + p_x;
      l_tmp_b := 1/(6-l_tmp_a);
      INSERT INTO test_tbl VALUES( l_tmp_a, l_tmp_b );
      p_a := l_tmp_a;
      p_b := l_tmp_b;
    EXCEPTION
      WHEN <<some exception>>
      THEN
        ROLLBACK TO SAVEPOINT s1;
    END;
    You could, of course, have the same TMP local variables in your original code. Or you could replicate the computations, i.e.
    for x in 1..5 loop
    begin
      savepoint s;
      insert into test_tbl values(a+x,1/(6-a));
      a := a+x;
      b := 1/(6-a);
    exception
      when others then
        rollback to savepoint s;
    end;
    Justin

Answers

  • JustinCave
    JustinCave Consultant Rochester Hills, MIMember Posts: 30,293 Gold Crown
    Sure
    SQL> create table foo( col1 number );
    
    Table created.
    
    SQL> ed
    Wrote file afiedt.buf
    
      1  declare
      2    eww_a_three exception;
      3  begin
      4    for i in 1..5
      5    loop
      6      begin
      7        savepoint s1;
      8        insert into foo values( i );
      9        if( i = 3 )
     10        then
     11          raise eww_a_three;
     12        end if;
     13      exception
     14        when eww_a_three then
     15          rollback to savepoint s1;
     16      end;
     17    end loop;
     18* end;
    SQL> /
    
    PL/SQL procedure successfully completed.
    
    SQL> select * from foo;
    
          COL1
    ----------
             1
             2
             4
             5
    Of course, if your code can be only partially successful, you'd need some way to alert the caller that it was only partially successful. That would normally involve raising an exception since you generally shouldn't depend on the caller to read the error log that you wrote to in order to discover that there was an error. And if the caller can't handle that exception, the entire transaction generally ought to be rolled back.

    Justin
    JustinCave
  • GVR
    GVR Member Posts: 439
    I hope this simple test would help you. It would error out when x=3 ;
    create table test_temp(x number,y number);
    
    create table error_log (error_msg varchar2(2000));
    
    truncate table test_temp;
    truncate table error_log;
    declare
    y number;
    begin
    for x in 1 ..5 loop
        begin
          insert into test_temp(x) values(x);
           y:=3/(x-3);
           insert into test_temp(x,y) values(x,y);
        commit;
        exception
            when others then
              rollback;
             insert into error_log values('failed for x=3; divide by zero');
             commit;
             
        end;
    end loop;
    end;
    /
    
    select * from test_temp;
    
    select * from error_log;
    GVR
  • 910971
    910971 Member Posts: 40
    edited May 30, 2012 4:48PM
    Thank you for the reply, but it prompted an additional question. Is there any way to rollback to the previously stored value of a PL/SQL variable? For instance:
    create table test_tbl(x number);
    
    declare
        cnt number;
        ex  exception;
    begin
        cnt := 0;
        for x in 1..5 loop
            begin
                savepoint s;
                cnt := cnt+1;
                insert into test_tbl values(x);
                if x = 3 then
                    raise ex;
                end if;
            exception
                when others then
                    rollback to savepoint s;
            end;
        end loop;
        dbms_output.put_line('cnt='||cnt);
    end;
    
    select * from test_tbl order by x;
    In this case, I'd like cnt to return to its previously stored value prior to the savepoint (cnt=2) and then continue incrementing from there, so the final value would be cnt=4.
  • JustinCave
    JustinCave Consultant Rochester Hills, MIMember Posts: 30,293 Gold Crown
    No. The values of PL/SQL variables aren't part of the transaction so they're not going to be rolled back.

    You could, of course, have a separate variable that you use to capture the value of CNT just before (or just after) declaring the savepoint and then restore that value when you do your rollback to savepoint. In this case, you could also move the
    cnt := cnt + 1;
    call to be after the IF statement so that it is only incremented if the insert completes successfully.

    Justin
    JustinCave
  • 910971
    910971 Member Posts: 40
    In this particular case that would work, but the example is rather contrived, as it's designed to fail specifically on the third iteration. In my actual code, I wouldn't be using an IF statement to deliberately raise an exception, but I'd want to catch any exceptions that might come up, like:
    declare
        cnt number;
        ex  exception;
    begin
        cnt := 0;
        for x in 1..5 loop
            begin
                savepoint s;
                cnt := cnt+1;
                insert into test_tbl values(1/(3-x));
            exception
                when others then
                    rollback to savepoint s;
            end;
        end loop;
        dbms_output.put_line('cnt='||cnt);
    end;
    So in summation, you're saying that savepoint/rollback/commit have no effect on PL/SQL variables (both local and global?) and I'll need to reset the value of cnt manually:
    declare
        cnt number;
        ex  exception;
    begin
        cnt := 0;
        for x in 1..5 loop
            begin
                savepoint s;
                cnt := cnt+1;
                insert into test_tbl values(1/(3-x));
            exception
                when others then
                    cnt := cnt-1;
                    rollback to savepoint s;
            end;
        end loop;
        dbms_output.put_line('cnt='||cnt);
    end;
  • JustinCave
    JustinCave Consultant Rochester Hills, MIMember Posts: 30,293 Gold Crown
    As I said, you can put the increment operation at the end of the block when you know no more exceptions could be raised, i.e.
    declare
        cnt number;
        ex  exception;
    begin
        cnt := 0;
        for x in 1..5 loop
            begin
                savepoint s;
                insert into test_tbl values(1/(3-x));
                cnt := cnt+1;
            exception
                when others then
                    rollback to savepoint s;
            end;
        end loop;
        dbms_output.put_line('cnt='||cnt);
    end;
    You could also increment the counter outside of the PL/SQL block, i.e.
    declare
        cnt number;
        ex  exception;
        num_success number;
    begin
        cnt := 0;
        for x in 1..5 loop
            begin
                num_success := 0;
                savepoint s;
                insert into test_tbl values(1/(3-x));
                num_success := sql%rowcount;
            exception
                when others then
                    rollback to savepoint s;
            end;
            cnt := cnt + num_success;
        end loop;
        dbms_output.put_line('cnt='||cnt);
    end;
    And you can refactor your code so that the components to hide some of this complexity, i.e.
    SQL> ed
    Wrote file afiedt.buf
    
      1  create or replace procedure try_insert_test_tbl(
      2    p_val in number,
      3    p_num_success out number )
      4  as
      5    divide_by_zero exception;
      6    pragma exception_init( divide_by_zero, -1476 );
      7  begin
      8    savepoint s;
      9    insert into test_tbl values( 1/(3-p_val ) );
     10    p_num_success := sql%rowcount;
     11  exception
     12    when divide_by_zero
     13    then
     14      p_num_success := 0;
     15* end;
    SQL> /
    
    Procedure created.
    
    SQL> ed
    Wrote file afiedt.buf
    
      1  declare
      2      cnt number;
      3      ex  exception;
      4      num_success number;
      5  begin
      6      cnt := 0;
      7      for x in 1..5 loop
      8          try_insert_test_tbl( x, num_success );
      9          cnt := cnt + num_success;
     10      end loop;
     11      dbms_output.put_line('cnt='||cnt);
     12* end;
    SQL> /
    cnt=4
    
    PL/SQL procedure successfully completed.
    There is no way for DCL statements to affect the state of PL/SQL variables. But you can generally structure your code in such a way that this is unnecessary.

    Justin
    JustinCave
  • Frank Kulash
    Frank Kulash Boston, USAMember, Moderator Posts: 43,002 Red Diamond
    edited May 30, 2012 5:37PM
    Hi,
    crottyan wrote:
    In this particular case that would work, but the example is rather contrived, as it's designed to fail specifically on the third iteration. In my actual code, I wouldn't be using an IF statement to deliberately raise an exception, but I'd want to catch any exceptions that might come up ...
    Doesn't that approach work, regardless of where and how often the errors occur? Post a specific example where it doesn't do what you want.
    So in summation, you're saying that savepoint/rollback/commit have no effect on PL/SQL variables (both local and global?)
    Exactly! ROLLBACK (and COMMIT) only affect pending DML operations.
    Say it's 9:00 AM. The sun is shining, and you have a full cup of hot coffee. You're executing a procedure called foo. Variable x is set to 0, and there are 100 rows in the table. At this time, you issue a SAVEPOINT.
    Now say it's 9:10. The sky has gotten cloudy, the coffee has cooled down and you've drunk half of it. Procedure foo has called procedure bar, and that's what is now running. Variable x is now set to 10, and you've INSERTed 10 rows, so there are 110 rows in the table (as you see it in your session. You haven't COMMITted the changes yet.)
    At this point you issue a ROLLBACK statement. There will now be 100 rows in the table again, just like there were at 9:00. However, the clock will still say 9:10, the sky will still be cloudy, your coffee cup will still be half full, the coffee will still be cool. You will still be executing procedure bar, and x will still be set to 10. Only the pending DML changes (the 10 new rows that were INSERTed) will revert to the state as of 9:00.
    and I'll need to reset the value of cnt manually:
    If you only want to count the sucessful INSERTs, then only count the sucessful INSERTs; don't count all of them:
    declare
        cnt number;
        ex  exception;
    begin
        cnt := 0;
        for x in 1..5 loop
            begin
                savepoint s;
                insert into test_tbl values(1/(3-x));
    
    	    -- The following statement is executed only if the INSERT above worked:
                cnt := cnt+1;
            exception
                when others then
                    rollback to savepoint s;
            end;
        end loop;
        dbms_output.put_line('cnt='||cnt);
    end;
    Frank Kulash
  • 910971
    910971 Member Posts: 40
    edited May 31, 2012 10:38AM
    Doesn't that approach work, regardless of where and how often the errors occur? Post a specific example where it doesn't do what you want.
    create table test_tbl(a number,b number);
    
    declare
        a  number;
        b  number;
    begin
        a := 0;
        b := 0;
        for x in 1..5 loop
            begin
                savepoint s;
                a := a+x;
                b := 1/(6-a);
                insert into test_tbl values(a,b);
            exception
                when others then
                    rollback to savepoint s;
            end;
        end loop;
    end;
    
    select * from test_tbl order by x;
    
    Results:
        a     b
        --    --
        1     .2
        3     .33
        10    -.25
        15    -.11
    The values of the last two records are now incorrect. Iteration 3 failed after the value of "a" was already updated, so it retains this incorrect value. I said my previous example was contrived because it is; I realize that, in that particular case, cnt could be placed after the INSERT in order to avoid the problem of an exception, but that didn't address the larger question about resetting a PL/SQL variable to its pre-exception state.
    Say it's 9:00 AM...
    I believe your vignette answers the question, but in case there was a miscommunication, I'll rephrase: I'd like any processing (changes to both the database and local variables) that occurs inside the FOR loop to be atomic. I know that changes to the database can be undone with a ROLLBACK, but, from what I understand, I'll need to manually reset the values of PL/SQL variables to their pre-exception state?
  • JustinCave
    JustinCave Consultant Rochester Hills, MIMember Posts: 30,293 Gold Crown
    In this particular case, I would tend to suggest that you follow the third example I posted where you factor out the core of your loop into a procedure such that you only overwrite the values of a and b if your insert succeeds, i.e.
    CREATE OR REPLACE PROCEDURE proc_name( p_x IN NUMBER, p_a IN OUT NUMBER, p_b IN OUT NUMBER )
    AS
      l_tmp_a NUMBER;
      l_tmp_b NUMBER;
    BEGIN
      SAVEPOINT s1;
      l_tmp_a := p_a + p_x;
      l_tmp_b := 1/(6-l_tmp_a);
      INSERT INTO test_tbl VALUES( l_tmp_a, l_tmp_b );
      p_a := l_tmp_a;
      p_b := l_tmp_b;
    EXCEPTION
      WHEN <<some exception>>
      THEN
        ROLLBACK TO SAVEPOINT s1;
    END;
    You could, of course, have the same TMP local variables in your original code. Or you could replicate the computations, i.e.
    for x in 1..5 loop
    begin
      savepoint s;
      insert into test_tbl values(a+x,1/(6-a));
      a := a+x;
      b := 1/(6-a);
    exception
      when others then
        rollback to savepoint s;
    end;
    Justin
This discussion has been closed.