How to commit a single DML statement in Oracle (PL/SQL)
Sometimes you might need to commit a single INSERT, UPDATE or 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.
