ss

Oracle Tips by Burleson

SAVEPOINT

A SAVEPOINT is a marker within a transaction that allows for a partial rollback. As changes are made in a transaction, we can create SAVEPOINTs to mark different points within the transaction. If we encounter an error, we can rollback to a SAVEPOINT or all the way back to the beginning of the transaction.

SQL> INSERT INTO AUTHOR

2 VALUES ('A11l', 'john',

3 'garmany', '123-345-4567',

4 '1234 here st', 'denver',

5 'CO','90204', '9999');

1 row created.

SQL> savepoint in_author;

Savepoint created.

SQL> INSERT INTO BOOK_AUTHOR VALUES ('A111', 'B130', .20);

1 row created.

SQL> savepoint in_book_author;

Savepoint created.

SQL> INSERT INTO BOOK

2 VALUES ('B130', 'P002', 'easy oracle sql',

3 'miscellaneous', 9.95, 1000, 15, 0, '',

4 to_date ('02-20-2005','MM-DD-YYYY'));

1 row created.

SQL> rollback to in_author;

Rollback complete.

In the example above, I inserted a row into the AUTHOR table and created a SAVEPOINT called in_author. Next, I inserted a row into the book_author table and created another SAVEPOINT called in_book_author. Finally, I inserted a row in the BOOK table. I then issued a ROLLBACK to in_author. At this point, the row inserted into the AUTHOR table is still there and not committed. The rows added to the book and book_author tables have been discarded. At this point, I can continue to make changes, issue a ROLLBACK to the start of the transaction, or issue a COMMIT and commit the row in the author table.

The above book excerpt is from:

Easy Oracle SQL

Get Started Fast writing SQL Reports with SQL*Plus

ISBN 0-9727513-7-8

John Garmany

http://www.rampant-books.com/book_2005_1_easy_sql.htm