Oracle PL/SQL for DBAs

The ROLLBACK Statement

When you perform a ROLLBACK, you undo some or all changes made by your session to the database in the current transaction. Why would you want to erase changes? From an ad hoc SQL standpoint, the ROLLBACK gives you a way to erase mistakes you might have made, as in:

    DELETE FROM orders;

“No, no! I meant to delete only the orders before May 1995!” No problem—just issue ROLLBACK. From an application coding standpoint, ROLLBACK is important because it allows you to clean up or restart from a clean state when a problem occurs.

The syntax for the ROLLBACK statement is:

    ROLLBACK [WORK] [TO [SAVEPOINT] savepoint_name];

There are two basic ways to use ROLLBACK: without parameters or with the TO clause to indicate a savepoint at which the ROLLBACK should stop. The parameterless ROLLBACK undoes all outstanding changes in your transaction.

The ROLLBACK TO version allows you to undo all changes and release all acquired locks that were issued since the savepoint identified by savepoint_name was marked. (See the next section on the SAVEPOINT statement for more information on how to mark a savepoint in your application.)

The savepoint_name is an undeclared Oracle identifier. It cannot be a literal (enclosed in quotes) or a variable name.

All of the following uses of ROLLBACK are valid:

    ROLLBACK;
    ROLLBACK WORK;
    ROLLBACK TO begin_cleanup;

When you roll back to a specific savepoint, all savepoints issued after the specified savepoint_name are erased, but the savepoint to which you ...