FOR Loop Exception Handling

910971

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?

JustinCave

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

JustinCave

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

GVR

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;

910971

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

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

910971

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

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

Frank Kulash

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;

910971

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

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