SAVEPOINT

Purpose

Use the SAVEPOINT statement to identify a point in a transaction to which you can later roll back.

See Also: Oracle8i Concepts for information on savepoints. ROLLBACK for information on rolling back transactions SET TRANSACTION for information on setting characteristics of the current transaction

Prerequisites

None.

Syntax

Keywords and Parameters

savepoint

Specify the name of the savepoint to be created.

Savepoint names must be distinct within a given transaction. If you create a second savepoint with the same identifier as an earlier savepoint, the earlier savepoint is erased. After a savepoint has been created, you can either continue processing, commit your work, roll back the entire transaction, or roll back to the savepoint.

Example

To update blake's and clark's salary, check that the total company salary does not exceed 27,000, then reenter clark's salary, enter:

UPDATE emp 
    SET sal = 2000 
    WHERE ename = 'BLAKE';
SAVEPOINT blake_sal;

UPDATE emp 
    SET sal = 1500 
    WHERE ename = 'CLARK';
SAVEPOINT clark_sal;

SELECT SUM(sal) FROM emp;

ROLLBACK TO SAVEPOINT blake_sal;
 
UPDATE emp 
    SET sal = 1200 
    WHERE ename = 'CLARK';
 
COMMIT;