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.
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;
begin/end around block B, but they may add clarity in this instance.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;
