如何得到过程运行中完整的错误提示

keepLearning

create or replace procedure scott.p_test
is
begin
insert into scott.emp(empno,ename,deptno) values(12,'bsd',33);
commit;
exception
  when others then
    ROLLBACK;
    dbms_output.put_line('SQLCODE:  '||SQLCODE);
    dbms_output.put_line('SQLERRM:  '||SQLERRM);
end;
如果是如上代码只能获取到一个错误提示
SQL> set serverout on;
SQL> exec scott.p_test;

SQLCODE:  -2291
SQLERRM:  ORA-02291: integrity constraint (SCOTT.FK_DEPTNO) violated - parent key not found

PL/SQL procedure successfully completed




如果是下面代码sqlplus就会给所以错误提示，包括行定位
ORA-02291: integrity constraint (SCOTT.FK_DEPTNO) violated - parent key not found
ORA-06512: at "SCOTT.P_TEST", line 4
ORA-06512: at line 2



create or replace procedure scott.p_test
is
begin
insert into scott.emp(empno,ename,deptno) values(12,'bsd',33);
commit;
/*exception
  when others then
    ROLLBACK;
    dbms_output.put_line('SQLCODE:  '||SQLCODE);
    dbms_output.put_line('SQLERRM:  '||SQLERRM);*/
end;

Liu Maclean(刘相兵

FYI

1. 
   
2. 
   
3. SQL> create table emp(empno int primary key, ename varchar2(2000),depno int);
   
4. 
   
5. Table created.
   
6. 
   
7. 
   
8. SQL> insert into emp values(1,'maclean',2);
   
9. 
   
10. 1 row created.
    
11. 
    
12. SQL> commit;
    
13. 
    
14. Commit complete.
    
15. 
    
16. SQL> set serveroutput on;
    
17. 
    
18. 
    
19. create or replace procedure p_test
    
20. is
    
21. begin
    
22. insert into emp(empno,ename,depno) values(1,'maclean',3);
    
23. commit;
    
24. exception
    
25.   when others then
    
26.     ROLLBACK;
    
27.     dbms_output.put_line('SQLCODE:  '||SQLCODE);
    
28.     dbms_output.put_line('SQLERRM:  '||SQLERRM);
    
29. end;
    
30. /
    
31. 
    
32. SQL> exec p_test;
    
33. SQLCODE:  -1
    
34. SQLERRM:  ORA-00001: unique constraint (SYS.SYS_C004814) violated
    
35. 
    
36. PL/SQL procedure successfully completed.
    
37. 
    
38. 
    
39. create or replace procedure p_test
    
40. is
    
41. begin
    
42. insert into emp(empno,ename,depno) values(1,'maclean',3);
    
43. commit;
    
44. exception
    
45.   when others then
    
46.     ROLLBACK;
    
47.     dbms_output.put_line('SQLCODE:  '||SQLCODE);
    
48.     dbms_output.put_line('SQLERRM:  '||SQLERRM);
    
49.        
    
50.         DBMS_OUTPUT.PUT_LINE( DBMS_UTILITY.FORMAT_ERROR_BACKTRACE );
    
51. end;
    
52. /
    
53. 
    
54. 
    
55. 
    
56. 
    
57. SQL> exec p_test;
    
58. SQLCODE:  -1
    
59. SQLERRM:  ORA-00001: unique constraint (SYS.SYS_C004814) violated
    
60. ORA-06512: at "SYS.P_TEST", line 4
    

复制代码