Create oracle table with auto commit on

Question

Here is the scenario, I am writing a procedure which performs DML(insert,delete) operations on multiple tables. In case of exception I need to rollback data from most of the tables except one.

Example:

Procedure{

1 Insert into table1

2 Delete from table2

3 Insert into Table3

4 Insert into table4

 Commit;

Exception

Rollback;

}

If there is any exception in 4th Insert, I need to rollback all previous transaction except 3rd one. I know, commit/rollback doesn't work like that. But maybe is there way while creating table3, I can set some parameter so that every INSERT statement on table3 will be auto commited.

Answer 1

It's possible using an Autonomous Transaction.

CREATE TABLE t1 (x INTEGER PRIMARY KEY);
CREATE TABLE t2 (x INTEGER);
CREATE TABLE t3 (x INTEGER);
CREATE TABLE t4 (x INTEGER REFERENCES t1(x));

Then run this anononymous PL/SQL. Look carefully, the local procedure upd_table_3 isn't run when it's declared. It's run when called within the main BEGIN block:

DECLARE 
  PROCEDURE upd_table_3 IS
  PRAGMA AUTONOMOUS_TRANSACTION;
  BEGIN
    INSERT INTO t3 VALUES (3);
    COMMIT;
  END;
BEGIN
  INSERT INTO t1 VALUES (1);
  INSERT INTO t2 VALUES (2);
  upd_table_3;
  INSERT INTO t4 VALUES (4);
  COMMIT;
END;
/

The procedure should fail intentionally on the 4th insert with ORA-02291. I made it this way. Then

ROLLBACK;

All the tables should be rolled back except t3, which we did the insert in an autonomous transaction.

SELECT * FROM t1;

no rows selected

SELECT * FROM t2;

no rows selected

SELECT * FROM t3;

         X
----------
         3

SELECT * FROM t4;

no rows selected