Save points in oracle

SAVEPOINT- name Marks a savepoint within the current transaction

ROLLBACK - ROLLBACK ends the current transaction by discarding all pending

                     data changes

ROLLBACK TO SAVEPOINT name-ROLLBACK TO SAVEPOINT rolls back the current transaction to the specified savepoint, thereby discarding any changes and or savepoints created after the savepoint to which you are rolling back. If you omit the TO SAVEPOINT clause, the ROLLBACK statement rolls back the entire transaction. As savepoints are logical, there is no way to list the savepoints you have created.

Syntax:
Savepoint <savepoint_name>;

Example:-
SQL> savepoint s1;
SQL> insert into student values(1, ‘a’, 100);
SQL> savepoint s2;
SQL> insert into student values(2, ‘b’, 200);
SQL> savepoint s3;
SQL> insert into student values(3, ‘c’, 300);
SQL> savepoint s4;
SQL> insert into student values(4, ‘d’, 400);

Before rollback 

SQL> select * from student;

NO NAME MARKS
--- ------- ----------
1 a 100
2 b 200
3 c 300
4 d 400

SQL> rollback to savepoint s3;
Or
SQL> rollback to s3;

This will rollback last two records.
SQL> select * from student;

NO NAME MARKS
--- ------- ----------
1 a 100
2 b 200

Example1:-

CREATE OR REPLACE PROCEDURE pp

IS

BEGIN

   UPDATE emp SET ename = ename || '1' WHERE empno = 7788;

   SAVEPOINT 1;

   UPDATE emp SET ename = ename || '2' WHERE empno = 7788;

   SAVEPOINT 2;

   UPDATE emp SET ename = ename || '3' WHERE empno = 7788;

   SAVEPOINT 3;

   UPDATE emp SET ename = ename || '4' WHERE empno = 7788;

   SAVEPOINT 4;

  

   ROLLBACK to 2;

  

   COMMIT;

END;

/

U will get compilation error like below:

PL/SQL: ORA-00933: SQL command not properly ended

Sol:- you can’t establish your savepoints using numbers, u should give characters.

Example2:-

CREATE OR REPLACE PROCEDURE pp

IS

BEGIN

   UPDATE emp SET ename = ename || '1' WHERE empno = 7788;

   SAVEPOINT a;

   UPDATE emp SET ename = ename || '2' WHERE empno = 7788;

   SAVEPOINT b;

   UPDATE emp SET ename = ename || '3' WHERE empno = 7788;

   SAVEPOINT c;

   UPDATE emp SET ename = ename || '4' WHERE empno = 7788;

   SAVEPOINT d;

  

     Commit;

   ROLLBACK to b;

  

   COMMIT;

END;

/

U will get run time error like below:

ORA-01086: savepoint 'B' never established

Sol:- you can’t establish your savepoints after commit statement, why because already transactions are committed using commit, so it is impossible to rollback.

Example3:-

CREATE OR REPLACE PROCEDURE pp

IS

BEGIN

   UPDATE emp SET ename = ename || '1' WHERE empno = 7788;

   SAVEPOINT a;

   UPDATE emp SET ename = ename || '2' WHERE empno = 7788;

   SAVEPOINT b;

   UPDATE emp SET ename = ename || '3' WHERE empno = 7788;

   SAVEPOINT c;

   UPDATE emp SET ename = ename || '4' WHERE empno = 7788;

   SAVEPOINT d;

  

    ROLLBACK to 2;

  

   COMMIT;

END;

/

Sol:- Here upto b transactions will be rollback, and before b whatever transactions are there those will be committed. Means SCOTT will become SCOTT12.