今天收到一个请求，问如何才能看到异常时所有的信息，其实我们可以测试一把：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
CREATE OR REPLACE PROCEDURE proc1 IS BEGIN DBMS_OUTPUT.put_line ('running proc1'); RAISE NO_DATA_FOUND; END; / CREATE OR REPLACE PROCEDURE proc2 IS l_str VARCHAR2 (30) := 'calling proc1'; BEGIN DBMS_OUTPUT.put_line (l_str); proc1; END; / CREATE OR REPLACE PROCEDURE proc3_a IS BEGIN DBMS_OUTPUT.put_line ('calling proc2'); proc2; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line ('Error stack at top level:'); DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_backtrace); END; / |
通过DBMS_UTILITY.format_error_backtrace，我们可以看到出错的行号，
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
test@ORA11G(10.10.10.27)> BEGIN 2 DBMS_OUTPUT.put_line ('Proc3 -> Proc2 -> Proc1 backtrace'); 3 proc3_a; 4 END; 5 / Proc3 -> Proc2 -> Proc1 backtrace calling proc2 calling proc1 running proc1 Error stack at top level: ORA-06512: at "TEST.PROC1", line 4 ORA-06512: at "TEST.PROC2", line 5 ORA-06512: at "TEST.PROC3_A", line 4 PL/SQL procedure successfully completed. Elapsed: 00:00:00.01 test@ORA11G(10.10.10.27)> |
但是输出的堆栈信息并不完整，我们并不知道是因为什么导致ORA-06512: at “TEST.PROC1”, line 4。想要看全部的信息？其实也很简单，加上DBMS_UTILITY.FORMAT_ERROR_STACK即可：
|
1 2 3 4 5 6 7 8 9 10 11 |
CREATE OR REPLACE PROCEDURE proc3_b IS BEGIN DBMS_OUTPUT.put_line ('calling proc2'); proc2; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line ('Error stack at top level:'); DBMS_OUTPUT.put_line (DBMS_UTILITY.FORMAT_ERROR_STACK||DBMS_UTILITY.format_error_backtrace); END; / |
运行一下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
test@ORA11G(10.10.10.27)> BEGIN 2 DBMS_OUTPUT.put_line ('Proc3 -> Proc2 -> Proc1 backtrace'); 3 proc3_b; 4 END; 5 / Proc3 -> Proc2 -> Proc1 backtrace calling proc2 calling proc1 running proc1 Error stack at top level: ORA-01403: no data found ORA-06512: at "TEST.PROC1", line 4 ORA-06512: at "TEST.PROC2", line 5 ORA-06512: at "TEST.PROC3_B", line 4 PL/SQL procedure successfully completed. Elapsed: 00:00:00.01 test@ORA11G(10.10.10.27)> |
此时，就能看到原因是ORA-01403: no data found。是调用TEST.PROC1时由于ORA-01403: no data found，所以导致后续的报错。