Roll back data inserted in a procedure : Insert « Stored Procedure Function « Oracle PL / SQL






  1. Oracle PL / SQL
  2. Stored Procedure Function
  3. Insert

Roll back data inserted in a procedure

  
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>








Related examples in the same category

1.Insert data into a table in a stored procedure
2.Use two insert statements in a procedure
3.Use stored procedure to insert value to a table and use select statement to check the result
4.Use a stored procedure to insert data to a table
5.Adjust salary with pl/sql
6.This procedure will insert a new book into the book table.