CODEWITHSUNDEEP
oracle-databaseoracle11gexception
user2671057
user2671057

Reputation: 1533

Oracle full error message in log

I'm trying to get the full error message from oracle.

For example - I have a very long procedure that doing a lot of manipulation on

a lot of objects, and in my log I got the error

object no longer exist.

And this is my insert to the log (even it is a generally question - not specific to this example):

EXCEPTION WHEN OTHERS THEN
       v_errno := sqlcode;
       V_ERRMSG := SQLERRM;
       INSERT INTO ERR_TABLE (ERROR_NUMBER, ERROR_MESSAGE,PROGRAM#)
       VALUES (V_ERRNO, V_ERRMSG,'MY_PKG');
       COMMIT;

The problem is that I don't know which table it talking about - because this

information doesn't exsits.

Is there a way to get it?

I guess that oracle save it in some place.

thanks!

Upvotes: 0

Views: 4271

Answers (1)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59456

For internal logging (not only for errors) I use a procedure like this:

PROCEDURE Put(
        LogMessage IN T_LOG_ENTRIES.LOG_MESSAGE%TYPE,
        ErrCode IN T_LOG_ENTRIES.LOG_ERROR_CODE%TYPE DEFAULT 0) IS

    ErrorStack T_LOG_ENTRIES.LOG_ERROR_STACK%TYPE;
    PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

    IF ErrCode <> 0 THEN
        ErrorStack := DBMS_UTILITY.FORMAT_ERROR_BACKTRACE();
        ErrorStack := SQLERRM(ErrCode) || CHR(13) || ErrorStack;
    END IF;

    INSERT INTO T_LOG_ENTRIES 
        (LOG_DATE, LOG_MESSAGE, LOG_ERROR_CODE, LOG_ERROR_STACK) 
    VALUES 
        (CURRENT_TIMESTAMP, LogMessage, ErrCode, ErrorStack);
    COMMIT;
END Put;

DBMS_UTILITY.FORMAT_ERROR_BACKTRACE() provides the full error stack. You should use AUTONOMOUS_TRANSACTION since transactions are rolled back in case of exception, i.e. your log message would be deleted also.

Then you can use the procedure for example as this:

BEGIN
...
EXCEPTION WHEN OTHERS THEN
   Put('Error in my procedure', sqlcode);
END;

Upvotes: 1

Related Questions