Geek Answers Handbook

Oracle log line number

I am currently working on registration errors in the procedure. The purpose of this procedure is to call exception handlers in other packages in the database and register errors that each program encounters. below is my code.

CREATE OR REPLACE PROCEDURE APMS.test_procedure AS procedure write_error_log (errcode number, errstr varchar2) is pragma autonomous_transaction; -- this procedure stays in its own new private transaction begin INSERT INTO error_log (ora_err_tmsp, ora_err_number, ora_err_msg, ora_err_line_no) values (CURRENT_TIMESTAMP, errcode, errstr, 'line number'); COMMIT; -- this commit does not interfere with the caller transaction. end write_error_log; BEGIN INSERT INTO mockdata VALUES ('data1', 'mockname', 'mockcity'); exception when others then write_error_log(sqlcode,sqlerrm); raise; END test_procedure; /

Currently, I just throw an error in my mock_data table to log an error in the error_log table and see if its functionality is working, how to determine the row number column number. I am a complete newbie, so any help would be appreciated. If anyone knows how I could use this procedure in other packages / procedures to register errors in other packages, which would also be awesome. I am here to find out that any feedback is welcome, I can expand this post even more if I am not clear.

+5
source share
2 answers

Try using DBMS_UTILITY.FORMAT_ERROR_BACKTRACE . You can look here for more information.

Something like this should do your code:

 CREATE OR REPLACE PROCEDURE APMS.test_procedure AS procedure write_error_log (errcode number, errstr varchar2,errline varchar2) is pragma autonomous_transaction; -- this procedure stays in its own new private transaction begin INSERT INTO error_log (ora_err_tmsp, ora_err_number, ora_err_msg, ora_err_line_no) values (CURRENT_TIMESTAMP, errcode, errstr, errline); COMMIT; -- this commit does not interfere with the caller transaction. end write_error_log; BEGIN INSERT INTO mockdata VALUES ('data1', 'mockname', 'mockcity'); exception when others then write_error_log(sqlcode,sqlerrm,DBMS_UTILITY.FORMAT_ERROR_BACKTRACE); raise; END test_procedure;
+2
source

To get a call stack, use the following:

  • dbms_utility.format_error_stack
  • dbms_utility.format_error_backtrace

For instance,

 SQL> declare 2 v1 integer := 1; 3 v2 integer := 0; 4 v3 integer; 5 procedure p1 (v1 in integer, v2 in integer, v3 out integer) is 6 begin 7 v3 := v1 / v2; 8 end; 9 procedure p2 (v1 in integer, v2 in integer, v3 out integer) is 10 begin 11 p1 (v1, v2, v3); 12 end; 13 begin 14 p2 (v1, v2, v3); 15 exception 16 when others then 17 dbms_output.put_line ('---------------------'); 18 dbms_output.put_line ('This is what you record in log table:'); 19 dbms_output.put (dbms_utility.format_error_stack); 20 dbms_output.put (dbms_utility.format_error_backtrace); 21 dbms_output.put_line ('---------------------'); 22 raise; 23 end; 24 / --------------------- This is what you record in log table: ORA-01476: divisor is equal to zero ORA-06512: at line 7 ORA-06512: at line 11 ORA-06512: at line 14 --------------------- declare * ERROR at line 1: ORA-01476: divisor is equal to zero ORA-06512: at line 22
+1
source

More articles:


All Articles