How to commit a single DML statement in Oracle (PL/SQL)
Posted: | Categories: Oracle, Programming | Tags: DML, Statement
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: