Oracle and PL/SQL Recipes: A Problem-Solution Approach

2-12. Performing a Transaction

Problem

You need to complete a series of INSERT or UPDATE statements in order to process a complete transaction. In doing so, you need to ensure that if one of the statements fails, that all of the statements are canceled so that the transaction is not partially processed.

Solution

Use the transaction control mechanisms that are part of PL/SQL, as well as SQL itself, in order to control your transactions. When all your statements have been completed successfully, issue a COMMIT to make them final. On the other hand, if one of the statements does not complete successfully, then perform a ROLLBACK to undo all the other changes that have been made and bring the database back to the state that it was in prior to the ...