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.