Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, VINOTHKUMAR.

Asked: November 13, 2018 - 6:45 am UTC

Last updated: November 13, 2018 - 11:28 am UTC

Version: 11G

Viewed 1000+ times

You Asked

can you create pragma autonomous in exception handling

and Chris said...

No. You can only define autonomous transactions at the top-level declaration of anonymous blocks:

create table t (
  c1 int
);

declare
  pragma autonomous_transaction;
begin
  insert into t values ( 1 );
  commit;
end;
/

begin
  declare
    pragma autonomous_transaction;
  begin
    insert into t values ( 2 );
    commit;
  end;
end;
/

PLS-00710: Pragma AUTONOMOUS_TRANSACTION cannot be specified here

begin
  raise zero_divide;
exception
  when zero_divide then
    declare
      pragma autonomous_transaction;
    begin
      insert into t values ( 3 );
      commit;
    end;
end;
/

PLS-00710: Pragma AUTONOMOUS_TRANSACTION cannot be specified here

select * from t;

C1   
   1 


If you need a separate transaction in your exception handler (e.g. for error-logging), define the autonomous transaction in a procedure. And call that.

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library