Oracle: Savepoints within execute immediate and ORA-01086

Question

Does anybody know, why i get an ORA-01086: savepoint 'SPX' never established in this session or is invalid in the following Code?

begin 
    rollback; --clear all Transactions
    execute immediate 'begin
                           savepoint SPX;
                           raise no_data_found;  
                       end;';
exception when no_data_found then 
    rollback to savepoint SPX;
end;

It is working if i don't use execute immediate:

begin 
    rollback; --clear all Transactions
    begin
        savepoint SPX;
        raise no_data_found;  
    end;
exception when no_data_found then 
    rollback to savepoint SPX;
end;

So is this an expected behaviour or is this something like a bug?

I'm using Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

Update: the following Example is also working, it uses dynamic SQL combined with Savepoints:

begin 
    rollback; --clear all Transactions
    execute immediate 'begin
                           savepoint SPX;
                       end;';
    rollback to savepoint SPX;
end;

Jeffrey Kemp Jeffrey Kemp · Accepted Answer · 2018-05-01T05:51:31

The problem is that before the exception propagates to the outer block, an implicit ROLLBACK occurs, which removes the savepoint that the block had created.

Here is an example that reproduces the problem but using static SQL:

begin 
    rollback; --clear all Transactions
    savepoint SPX;
    rollback;
    raise no_data_found;
exception when no_data_found then 
    rollback to savepoint SPX;
end;

ORA-01086: savepoint 'SPX' never established in this session or is invalid ORA-06512: at line 7

Unfortunately you're stuck with this behaviour; dynamic SQL must be run within its own context, and if it raises an exception (and doesn't handle it), a ROLLBACK will be issued.

You will need to create the savepoint before running the dynamic SQL; alternatively, you can suppress the ORA-01086 error:

begin 
    rollback; --clear all Transactions
    execute immediate 'begin
                           savepoint SPX;
                           raise no_data_found;  
                       end;';
exception when no_data_found then 
  begin
    rollback to savepoint SPX;
  exception when others then
    if sqlcode != -1086 /*savepoint never established*/ then
      raise;
    end if;
  end;
end;

Oracle: Savepoints within execute immediate and ORA-01086

2 Answers