Mega Code Archive
Roll back data inserted in a procedure
<![CDATA[
SQL>
SQL> create table MyTable(n number );
Table created.
SQL>
SQL>
SQL> create table myLogTable(
2 username varchar2(30),
3 date_time timestamp,
4 message varchar2(4000) );
Table created.
SQL>
SQL>
SQL> create or replace
2 procedure log_message( p_message varchar2 ) as
3 pragma autonomous_transaction;
4 begin
5 insert into myLogTable( username, date_time, message )
6 values ( user, current_date, p_message );
7 commit;
8 end log_message;
9 /
Procedure created.
SQL>
SQL>
SQL> select * from MyTable;
no rows selected
SQL>
SQL> select * from myLogTable;
no rows selected
SQL>
SQL> begin
2 log_message( 'About to insert into MyTable' );
3 insert into MyTable( n )
4 values( 12345 );
5 log_message( 'rolling back insert into MyTable' );
6 rollback;
7 end;
8 /
PL/SQL procedure successfully completed.
SQL>
SQL> select * from MyTable
2
SQL> select * from myLogTable
2
SQL> drop table myLogTable;
Table dropped.
SQL> drop table MyTable;
Table dropped.
SQL>
]]>