ORA-01086: savepoint never established in this session or is invalid

SAVEPOINT is transaction control language in SQL , PL/SQL used to manage transaction consistent state within a complex coding during transition from one object to other.



It mainly helps to save a particular state of transaction and rollback to that point.





Let's see why it causes error "ORA-01086: savepoint never established in this session or is invalid" and solution for the same in two different scenario.



Created a test table to demo how the savepoint works and in which situation it encounters ORA-01086 error.



SQL> create table test_savepoint ( id number , savepnt varchar2(100));
Table created.



Scenario 1 : ORA-01086 error when roll forward attempted after a successful SAVEPOINT rollback




SQL> insert into test_savepoint values ( 1, 'savepoint 1');
1 row created.





SQL> SAVEPOINT a1;
Savepoint created.


SQL> select * from test_savepoint;
ID         SAVEPNT
---------- -------------
1 savepoint 1


SQL> insert into test_savepoint values ( 2 ,'savepoint 2');
1 row created.


SQL> SAVEPOINT a2;
Savepoint created.


SQL> select * from test_savepoint;
ID          SAVEPNT
----------  ---------------------
1 savepoint 1
2 savepoint 2

SQL> ROLLBACK to a1;
Rollback complete.


SQL> select * from test_savepoint;
ID         SAVEPNT
---------- ---------------------
1 savepoint 1

SQL> ROLLBACK to a2;
rollback to a2
*
ERROR at line 1:
ORA-01086: savepoint 'A2' never established in this session or is invalid



Scenario 2 : ORA-01086 error when rollback to SAVEPOINT is attempted after a commit transaction point


SQL> insert into test_savepoint values ( 5 ,'savepoint 5');
1 row created.





SQL> SAVEPOINT a5;
Savepoint created.


SQL> select * from test_savepoint;
ID         SAVEPNT
---------- ----------------------
1 savepoint 1
3 savepoint 3
5 savepoint 5


SQL> insert into test_savepoint values ( 6,'savepoint 6');
1 row created.


SQL> select * from test_savepoint;
ID         SAVEPNT
---------- ------------------
1 savepoint 1
3 savepoint 3
5 savepoint 5
6 savepoint 6


SQL> SAVEPOINT a6;
Savepoint created.


SQL> commit;
Commit complete.





SQL> rollback to a5;
rollback to a5
*
ERROR at line 1:

ORA-01086: savepoint 'A5' never established in this session or is invalid



So , ORA-01086 error generally appears when explicit commit is performed within a PL/SQL block ; or a ROLLBACK attempted which is actually a ROLL FORWARD within a transaction.