Oracle PL/SQL for DBAs

DML and Exception Handling

When an exception occurs in a PL/SQL block, Oracle does not roll back any of the changes made by DML statements in that block. It is up to you, the manager of the application’s logical transaction, to decide what sort of behavior should occur. Here are some things to keep in mind in this regard:

• If your block is an autonomous transaction (described later in this chapter), then you must perform a rollback or commit (usually a rollback) when an exception is raised.

• You can use savepoints to control the scope of a rollback. In other words, you can roll back to a particular savepoint and thereby preserve a portion of the changes made in your session. Savepoints are also explored later in this chapter.

• If an exception propagates past the outermost block (i.e., it goes unhandled), then, in most host execution environments for PL/SQL, like SQL*Plus, an unqualified rollback is automatically executed, reversing any outstanding changes.