Mega Code Archive
Rollback to savepoint in exception handler
<![CDATA[
SQL>
SQL> create table myTable2 ( cnt int );
Table created.
SQL>
SQL> insert into myTable2 values ( 0 );
1 row created.
SQL>
SQL> create table t ( x int check ( x>0 ) );
Table created.
SQL>
SQL> create trigger t_trigger before insert or delete on t for each row
2 begin
3 if ( inserting ) then
4 update myTable2 set cnt = cnt +1;
5 else
6 update myTable2 set cnt = cnt -1;
7 end if;
8 dbms_output.put_line( 'fired and updated ' || sql%rowcount || ' rows' );
9 end;
10 /
Trigger created.
SQL>
SQL> create or replace procedure p
2 as
3 begin
4 insert into t values ( 1 );
5 insert into t values (-1 );
6 end;
7 /
Procedure created.
SQL> select * from t;
no rows selected
SQL> select * from myTable2;
CNT
----------
0
SQL> begin
2 p;
3 end;
4 /
fired and updated 1 rows
fired and updated 1 rows
begin
*
ERROR at line 1:
ORA-02290: check constraint (RNTSOFT.SYS_C0010265) violated
ORA-06512: at "RNTSOFT.P", line 5
ORA-06512: at line 2
SQL> select * from t;
no rows selected
SQL> select * from myTable2;
CNT
----------
0
SQL>
SQL>
SQL> begin
2 p;
3 exception
4 when others then null;
5 end;
6 /
fired and updated 1 rows
fired and updated 1 rows
PL/SQL procedure successfully completed.
SQL> select * from t;
X
----------
1
SQL> select * from myTable2;
CNT
----------
1
SQL>
SQL> rollback;
Rollback complete.
SQL>
SQL> begin
2 savepoint sp;
3 p;
4 exception
5 when others then
6 rollback to sp;
7 end;
8 /
fired and updated 1 rows
fired and updated 1 rows
PL/SQL procedure successfully completed.
SQL> select * from t;
no rows selected
SQL> select * from myTable2;
CNT
----------
0
SQL>
SQL>
SQL>
SQL> drop table t;
Table dropped.
SQL> drop table myTable2;
Table dropped.
]]>