declare
v_name varchar2(2) := 'ABC';
begin
dbms_output.put_line(v_name);
exception
when others then
dbms_output.put_line('xxx');
end;
exception
when others then
dbms_output.put_line('rrrrrr'||sqlcode || sqlerrm);
end;
声明区域的异常,直接是传递到了最外层
rrrrrr-6502ORA-06502: PL/SQL: 数字或值错误 : 字符串缓冲区太小
在异常中恢复,将异常的处理放到子块中,这样在处理完异常后,可以继续进行下面的语句操作。
declare
e_duplicate_name exception;
v_name emp.ename%type;
v_newname emp.ename%type := 'smith';
begin
begin
select ename into v_name from emp where empno = 123;
if v_name = v_newname then
raise e_duplicate_name;
end if;
exception
when e_duplicate_name then
v_newname = 'jack';
end;
insert into emp values (123, v_newname);
exception
when others then
dbms_output.put_line(sqlcode || sqlerrm);
end;
获取异常程序的位置,在oracle10g后,有dbms_utility.format_error_backtrace,可以用来跟踪堆栈
when others then
dbms_output.put_line(sqlcode || sqlerrm||dbms_utility.format_error_backtrace);
end;