3

I have an Oracle package which loops through a list of procedures and calls them dynamically. Prior to each procedure call, a SAVEPOINT is created, and if an exception is raised, it issues a rollback and logs the problem. A bug was recently introduced where one of the dynamic procedures had a COMMIT added to it, which when triggered, invalidates the SAVEPOINT. If that same procedure then fails (raises an exception) and the calling package's exception handler attempts to rollback, the following exception is raised inside the exception handler block:

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

Now, I could put another exception handler in my exception handler, to handle this specific exception, but it would be much neater if I could do a quick check of the SAVEPOINT to see if it is valid or not immediately prior to attempting to issue the rollback. Is this possible?

ninesided
  • 23,085
  • 14
  • 83
  • 107

1 Answers1

2

I think you're going to have to forget it; sorry. Someone asked Tom Kyte this question in April 2013, his response:

2) where does oracle keep track of savepoint we create?And is SCN being created for the savepoints created?

and we said...

...

2) a savepoint is just conceptually a pointer into your undo stream. We do not need an SCN for it, we just need to know how far back in your undo stream to rollback to.

Both Jonathan Lewis and Burleson don't think they're stored anywhere in the data dictionary. If you're using Workspace Manager or want to use Restore Points then the data will be stored in ALL_WM_MODIFIED_TABLES and V$RESTORE_POINT respectively, but this seems like overkill.

Ben
  • 51,770
  • 36
  • 127
  • 149
  • I suspected that this was the case, but couldn't find the confirmation I was looking for, which you have kindly provided. Thank you, Sir. – ninesided Nov 04 '13 at 13:48