"ORA-01086: savepoint&…

***Checked for relevance on 30-Jan-2012*** 

Problem Description: 

==================== 

 

In your PL/SQL script, you mark a savepoint, call a stored procedure, and  

then try to ROLLBACK to the savepoint.   When you try to execute the  

ROLLBACK statement, you get the error "ORA-01086: savepoint 'S1'  

never established". 

 

Example: 

 

SQL> CREATE TABLE tab1 (c1 NUMBER, c2 NUMBER); 

 

Table created. 

 

SQL> INSERT INTO tab1 VALUES(1,1); 

 

1 row created. 

 

SQL> COMMIT; 

 

Commit complete. 

 

SQL> SELECT * FROM tab1; 

 

        C1         C2 

---------- ---------- 

          1           1 

 

SQL>  

SQL> CREATE PROCEDURE proc01 AS 

  2   BEGIN 

  3     INSERT INTO tab1 VALUES(1,1); 

  4     COMMIT; 

  5   END; 

  6   / 

 

Procedure created. 

 

SQL>  

SQL> BEGIN 

  2     UPDATE tab1 SET c1=10 WHERE c2=1; 

  3     SAVEPOINT s1; 

  4     INSERT INTO tab1 VALUES(2,2); 

  5     proc01; 

  6     ROLLBACK TO s1; 

  7   END; 

  8   / 

BEGIN 

* 

ERROR at line 1: 

ORA-01086: savepoint 'S1' never established 

ORA-06512: at line 1 

 

SQL>  

SQL> SELECT * FROM tab1; 

 

        C1         C2 

---------- ---------- 

        10           1 

          2           2 

          1           1 

 

 

Additional Search Words: ORA-1086 ORA-6512

Solution Description: 

===================== 

 

The behavior you are seeing is correct.   In the stored procedure, there is  

a COMMIT statement.   COMMIT will erase all savepoints.   You get the  

error ORA-01086 because the savepoint no longer exists.