Hi all,

Just want to share the Concept of using Savepoint in DML Statements
(transactional SQL).

Example:

CREATE TABLE TEST_SAVEPT
(
ID NUMBER PRIMARY KEY,
NAME VARCHAR2(100)
);

INSERT INTO TEST_SAVEPT VALUES (1,'Test1');
INSERT INTO TEST_SAVEPT VALUES (2,'Test2');
INSERT INTO TEST_SAVEPT VALUES (3,'Test3');
INSERT INTO TEST_SAVEPT VALUES (4,'Test4');
INSERT INTO TEST_SAVEPT VALUES (5,'Test5');
SAVEPOINT S_1;
INSERT INTO TEST_SAVEPT VALUES (6,'Test6');
INSERT INTO TEST_SAVEPT VALUES (7,'Test7');
INSERT INTO TEST_SAVEPT VALUES (8,'Test8');
INSERT INTO TEST_SAVEPT VALUES (9,'Test9');
INSERT INTO TEST_SAVEPT VALUES (10,'Test10');
SAVEPOINT S_2;
INSERT INTO TEST_SAVEPT VALUES (11,'Test11');
INSERT INTO TEST_SAVEPT VALUES (12,'Test12');
INSERT INTO TEST_SAVEPT VALUES (13,'Test13');
INSERT INTO TEST_SAVEPT VALUES (14,'Test14');
INSERT INTO TEST_SAVEPT VALUES (15,'Test15');

ROLLBACK TO S_2;
COMMIT;

SELECT * FROM TEST_SAVEPT;

ID NAME
1 Test1
2 Test2
3 Test3
4 Test4
5 Test5
6 Test6
7 Test7
8 Test8
9 Test9
10 Test10

Like this data can be rollbacked to a particular limit(on basis of the specified Savapoint).

The Values inserted after the savepoint S_2 alone will be rollbacked from the table.

__________________
-Murali..