Getting error "ORA-01086: savepoint 'SP_ONE' never established"

[slicendice]

Hi
I have a small PL/SQL script that is being run by a customer. It's nothing complex and is basically inserting various records based on various criteria. There are several discreet blocks within the code with each having it's own exception handler, which will print diagnostic messages and then RAISE. The outer exception handler performs a ROLLBACK to a defined save point.
At least, it should! It seems that when an error occurs, the "ROLLBACK TO" is generating:

ORA-01086: savepoint 'SP_ONE' never established

...even though savepoint SP_ONE does exist in the code.

I've simplified the script to try and illustrate what the code is doing:

FOR var IN cursor LOOP
  IF condition THEN
    BEGIN
      SAVEPOINT sp_One;

      BEGIN

        INSERT INTO ...;

        EXCEPTION
          WHEN OTHERS THEN
            dbms_output.put_line(<diagnostic msg>);
            RAISE;
      END;

      BEGIN

        INSERT INTO ...;

        EXCEPTION
          WHEN OTHERS THEN
            dbms_output.put_line(<diagnostic msg>);
            RAISE;
      END;

      BEGIN

        INSERT INTO ...;

        EXCEPTION
          WHEN OTHERS THEN
            dbms_output.put_line(<diagnostic msg>);
            RAISE;
      END;

      COMMIT;
    
      EXCEPTION
        WHEN OTHERS THEN
          dbms_putput.put_line(<error message>);
          ROLLBACK TO sp_One;
    END;
  END IF;
END LOOP;

Can anyone help me out with why this error is occurring?

Many thanks

 

[taupirho]

I believe the reason for your problems lies in the commit statement you have in your code. Standard Commits (or rollbacks) delete any existing savepoints set up. Why it does not get reset in your next loop iteration I don't know - perhaps you have some other inplicit commits (or rollbacks) going on somewhere else. Without seeing your whole code its difficult to tell.


In order to understand recursion, you must first understand recursion.