> BEGIN
> -- do something perilous
> EXCEPTION
> WHEN OTHERS THEN -- nothing much
> END;
> IF SQLSTATE = '42000' THEN ...
I understand. My idea was detect local exception for local block, I can't
to see exception's information outside block and I cant get exception's
info from inner block. Your idea is easy for implementation, but oracle
http://www.unix.org.ua/orelly/oracle/prog2/ch13_03.htm
In Oracle doc:
If no exception has been raised, SQLCODE returns zero and SQLERRM returns
the message: ORA-0000: normal, successful completion.
If you reference SQLCODE outside of an exception section, it always
returns 0, which means normal, successful completion.
I tested it on Oracle 10g
return integer as
begin
begin
dbms_output.put_line('1: '||SQLCODE||' -> '||SQLERRM);
raise_application_error(-20001, 'First exception');
exception when others then
dbms_output.put_line('2: '||SQLCODE||' -> '||SQLERRM);
begin
dbms_output.put_line('3: '||SQLCODE||' -> '||SQLERRM);
raise_application_error(-20002, 'Second exception');
exception when others then
dbms_output.put_line('4: '||SQLCODE||' -> '||SQLERRM);
end;
dbms_output.put_line('5: '||SQLCODE||' -> '||SQLERRM);
end;
dbms_output.put_line('6: '||SQLCODE||' -> '||SQLERRM);
return 1;
end;
select foo from dual
1: 0 -> ORA-0000: normal, successful completion
2: -20001 -> ORA-20001: First exception
3: -20001 -> ORA-20001: First exception
4: -20002 -> ORA-20002: Second exception
5: 0 -> ORA-0000: normal, successful completion
6: 0 -> ORA-0000: normal, successful completion
What it is mean?
So we can have only one procedure level scope variable, which is
initialized on start of exception and zeroized on the end of exception
block. This behavior is different from my patch, but is better for Oracle
compatibility and I prefere its.
I'll change patch, I can simplify it, if there will be agreement.
Best regards
Pavel Stehule
