Quick Search:
CODE
Oracle PL/SQL Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
 The Oracle PL/SQL SAVEPOINT Statement      [Return To Index] Jump to:  
  Looking for the original pages? (formerly called "Morgan's Library") You can find them here.

Term: SAVEPOINT

Definition:
In Oracle PL/SQL, SAVEPOINT is a TCL (Transaction Control Language) statement that creates a break point as a specified location in the current transaction. A transaction can be partially rolled back to any one of the savepoints. If multiple SAVEPOINT locations are set in the transaction, they are identified by their names, which must be unique. The name specification is optional for a single SAVEPOINT in the transaction.

Note that a COMMIT removes all the savepoints which may have been set earlier in the transaction.

Example Syntax:

SAVEPOINT [NAME]


Example Usage:

In the example below, SAVEPOINT A is set at the start of the transaction. A row is inserted into the TEST table. Another SAVEPOINT B is set just after the first insert. Now a second row is inserted into the TEST table. The transaction has to be rolled back to exclude the second insert. A rollback to savepoint B would undo the change done after savepoint B.

SQL> SAVEPOINT A

SQL> INSERT INTO TEST VALUES (1,'Savepoint A');
1 row inserted.

SQL> SAVEPOINT B

SQL> INSERT INTO TEST VALUES (2,'Savepoint B');
1 row inserted.

SQL> ROLLBACK TO B;

Rollback complete.

SQL> SELECT * FROM TEST;

ID MSG
-------- -----------
1 Savepoint A



Related Links:
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us 76 users online    © 2009 psoug.org
PSOUG LOGIN
Username: 
Password: 
Forgot your password?