logging line number in oracle

Question

I'm currently working on logging errors in a procedure. The goal of this procedure is to be called upon in exception handlers in the other packages in the DB and log the 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's 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;
/

I'm currently just inducing an error in my mock_data table to log the error in the error_log table and see if its functional I just cant figure out how to log the line number column. I'm a complete beginner so any help would appreciated. Addiotionally, If anybody knows how I would be able to use this procedure in other packages/procedures to log the errors in other packages that would be awesome as well. I'm here to learn so any feedback is appreciated, I can further expand on this post if i'm not being clear.

Answer 1

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

Something like this should make works 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's 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;