how to insert the result of dbms_output.put_line to a table for error catch?

Question

Exception
WHEN OTHERS THEN
--dbms_output.put_line('pl_update_sidm_user r: ERROR CODE:' || sqlcode || '~' || 
--sqlerrm || ' EMPL_NBR:' || r.EMPL_NBR);

insert into ERROR_MSG (ERROR_MSG_ID,ERROR_MSG) values (ERROR_MSG_ID_SEQ.NEXTVAL, 'pl_update_sidm_user_duty_role r2');
END;

I would like to put the error result to a table. However, how can I do that?

Can I put the result of dbms_output to a table as a string?

If not, can I get the sqlcode,sqlerrm without using dbms_output?

Thank you !!

You should fix the question title in order to make it more useful for other people facing the same problem; you are not asking how to insert the output of dbms_output: you are asking how to log errors in a table... that's not the same thing — Carlo Sirna, Nov 28 '18 at 06:10

score 2 · Answer 1 · answered Nov 28 '18 at 04:11

From the documentation,

A SQL statement cannot invoke SQLCODE or SQLERRM. To use their values in a SQL statement, assign them to local variables first

Also,

Oracle recommends using DBMS_UTILITY.FORMAT_ERROR_STACK except when using the FORALL statement with its SAVE EXCEPTIONS clause

So, for SQLCODE or SQLERRM, you should assign them into variables and use them.

DECLARE
v_errcode NUMBER;
v_errmsg VARCHAR2(1000);
BEGIN
--some other statements that may raise exception.
EXCEPTION
WHEN OTHERS THEN
v_errcode := SQLCODE;
v_errmsg  := SQLERRM;
insert into ERROR_TABLE (ERROR_MSG_ID,ERROR_MSG) --change your table name
values (ERROR_MSG_ID_SEQ.NEXTVAL,
      v_errcode||':'||v_errmsg); 

END;
/

Preferably use insert like this instead, as per Oracle's recommendation.

insert into ERROR_TABLE (ERROR_MSG_ID,ERROR_MSG) values (ERROR_MSG_ID_SEQ.NEXTVAL,
      DBMS_UTILITY.FORMAT_ERROR_STACK);

Demo

score 1 · Answer 2 · edited Jun 20 '20 at 09:12

Technically what others are suggesting is correct: the "insert" operation executed in the "exception when others" block will actually insert a new row in the log table.

the problem is that such insert statement will be part of the same transaction of the main procedure and, since you had an error while executing it, you are very likely to rollback that transaction, and this will rollback also the insert in your log table

I suppose the problem you are facing is not that you aren't successfully logging the error message: it is that you are rolling it back immediately afterwards, along with all the other writes you did in the same transaction.

Oracle gives you a way of executing code in a SEPARATE transaction, by using "autonomous transaction" procedures.

you need to create such a procedure:

create or replace  procedure Write_Error_log(
              arg_error_code number, 
              arg_error_msg varchar2, 
              arg_error_backtrace varchar2) is
 PRAGMA AUTONOMOUS_TRANSACTION;
 begin
    INSERT INTO error_msg (
          error_msg_id, 
          error_code, 
          error_msg, 
          error_stack)
     VALUES (
          error_msg_id_seq.NEXTVAL, 
          arg_error_code,
          arg_error_msg,  
          arg_error_backtrace);
     commit; -- you have to commit or rollback always, before exiting a 
             -- pragma autonomous_transaction procedure
 end;

What this procedure does is to write a new record in the log table using a totally separate and independent transaction: the data will stay in the log table even if you execute a roll back in your calling procedure. You can also use such a procedure to create a generic log (not only errors).

All you have to do now is to call the procedure above whenever you need to log something, so your code becomes:

DECLARE
   v_errcode NUMBER;
   v_errmsg VARCHAR2(1000);
BEGIN
   --some other statements that may raise exception.
EXCEPTION WHEN OTHERS THEN
     Write_Error_log(SQLCODE, SQLERRM, dbms_utility.format_error_backtrace);
END;
/

P.S: there might be some typos in my code: I can't test it right now since I can't reach an oracle server in this moment.

how to insert the result of dbms_output.put_line to a table for error catch?

2 Answers2