1

In my case, savepoint was set in Java and I am trying to rollback in a plsql procedure which was called in Java using a callable statement.

There are no commits or rollbacks in between, but I am still seeing the error:

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

Is it because the savepoint is set in Java and I am trying to rollback in plsql? Can someone please help me with this?

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
Mounika
  • 371
  • 4
  • 18
  • 5
    How do you set the savepoint from java? Show us your code. – Pavel Smirnov Mar 22 '19 at 09:15
  • 3
    Are you sure this is all in the same session; and there hasn't been an *auto*commit in between (as the savepoint and PL/SQL call are, presumably, two separate calls to the DB)? – Alex Poole Mar 22 '19 at 09:20
  • 3
    Looks like, java and plsql procedure are considered as 2 different transactions. Issue got fixed for me when I set the savepoint inside the PL/SQL procedure and rollback also inside the procedure. – Mounika Mar 22 '19 at 09:39
  • Glad that worked for you. You can answer your own question with the information in your comment, then accept your answer. Doing this will help others find a good solution. Thanks! – Mark Stewart Aug 03 '21 at 19:26

2 Answers2

1

Just throwing some light on this, My colleague experienced the similar issue when he controls the transaction but I had a commit inside the stored procedure. Remove the Commit/Rollback from the SP and you should be all set if you want to control the transaction from Java.

Venkat
  • 107
  • 12
0

Java and plsql procedure are considered as 2 different transactions. Issue got fixed for me when I set the savepoint inside the PL/SQL procedure and rollback also inside the procedure.

Mounika
  • 371
  • 4
  • 18