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, a feature that has been 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 procedure from another transaction (the 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 the changes made within p_insert will remain permanent. However, the changes made in your main transaction will be rolled back.

A typical scenario where you need an autonomous transaction is a table used to log errors and/or other information. For example, if something goes wrong you might want to log the reason into a table before rolling back the main transaction. In such a case you would write a procedure using the pragma AUTONOMOUS_TRANSACTION and call this procedure before rolling back.