Sometimes you might need to commit a single
DELETE statement and not the whole main transaction.
In such a case you can use an
AUTONOMOUS TRANSACTION which is a feature available since Oracle 8i.
The trick is to encapsulate the statement in a procedure and use the pragma
AUTONOMOUS_TRANSACTION. Here is an example, suppose you have a table,
your_table, which has just one column of type
NUMBER and you want to insert the value 1, irrespective of the state of the main transaction:
CREATE OR REPLACE PROCEDURE p_insert AS PRAGMA AUTONOMOUS_TRANSACTION; BEGIN -- Main transaction suspends here. INSERT INTO your_table VALUES (1); -- Autonomous transaction begins here. COMMIT; -- Autonomous transaction ends here. END; -- Main transaction resumes here.
After calling this proc from another transaction (main transaction) your_table will have another row with 1 as the
value of its only column. If, after calling
p_insert, you call
ROLLBACK in your main transaction you will
have that the changes made within
p_insert will be permanent. However the changes made in your main transaction will be rollbacked.
A tipical scenario where you need an autonomous transaction is a table used to log errors and/or other info.
For example if something goes wrong you might want to log the reason into a table before rollbacking the main transaction.
In such a case you would write a procedure using the pragma
AUTONOMOUS_TRANSACTION and call this proc before rollbacking.