Niraj Choubey
Niraj Choubey

Reputation: 4040

Exception handling in pl/sql

I have a stored procedure

create or replace procedure Trial
    is 
Begin
---Block A--
EXCEPTION
when others then
insert into error_log values('error');
--Block A ends----
--Block B ----
----Block B ends---
end;

I want code in Block B to execute in all condition i.e if exception in Block A is raised or not.With the above code Block B executes only if exception is raised. How to do this. Please help.

Upvotes: 6

Views: 3459

Answers (2)

HAL 9000
HAL 9000

Reputation: 3985

Please note that it's a common antipattern to catch all exceptions without raising them. You might also want to consider an autonomous transaction in order to keep the error-log after a rollback.

So you'd probably be better off with something like this:

create or replace procedure Trial
is 

  procedure finally is
  begin
    --Block B ----
  end;

  procedure logerr (msg varchar2) is
    PRAGMA AUTONOMOUS_TRANSACTION;
  begin
    insert into error_log values(msg);
    commit;
  end;

Begin
  begin
    ---Block A--
  EXCEPTION
    when others then
      logerr(SQLERRM);
      finally;
      raise;
  end;

  finally;
end;

Upvotes: 3

Tony Andrews
Tony Andrews

Reputation: 132710

You can created nested blocks:

create or replace procedure Trial
    is 
Begin
  begin
    ---Block A--
  EXCEPTION
    when others then
      insert into error_log values('error');
  end;
  begin
    --Block B ----
  end;
end;

Upvotes: 7

Related Questions