Database logging in logging procedure

Question

Perhaps a silly question, but say I have a logging procedure like so (greatly simplified for brevity):

 PROCEDURE LOGGER (
    p_TYPEOFLOG             IN  VARCHAR2,
    p_LOGSEVERITY           IN  VARCHAR2,
    p_SESSIONID             IN  VARCHAR2)
 AS
    v_timestamp             DATE := SYSDATE;

 BEGIN

    PRAGMA autonomous_transaction;

    BEGIN
        INSERT INTO Log (
            TypeOfLog, LogSeverity, SessionID, TimeOfAction)
        VALUES (
            p_TYPEOFLOG, p_LOGSEVERITY, p_SESSIONID, v_timestamp);
        COMMIT;
    END;

END LOGGER;

Now, usually I would wrap the begin/end bit up with an exception handler, which would call this logger procedure. But what do I do if I'm already in the Logger? Do I simply do an exception if when others null and skip it? Do I try calling the Logger procedure again? What is standard here?

If you called the Logger procedure again, and that failed again - if it's a wider problem like running out of space, say - how would you prevent infinite recursion adding to your problems? Oracle might try to kill the recursion, but you'd catch and try to log that too... — Alex Poole
– Alex Poole, Commented Nov 6, 2014 at 16:28

Justin Cave · Accepted Answer · 2014-11-06 16:07:09Z

4

I don't believe that there is such a thing as "standard".

Personally, if my logging code failed, I'd let whatever exception was generated be thrown and propagate up the stack. If you can't write to the Log table, that implies that something terribly unfortunate has happened. I wouldn't want to catch and ignore such an exception. And given no other way to log the error, I'd raise the exception back to the caller and expect the caller to either display the message to a user (i.e. a stack trace in the front end web app) or to log the message to an application server log.

answered Nov 6, 2014 at 16:07

Justin Cave

233k25 gold badges378 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

peihan Over a year ago

My PL/SQL logging utility is here. Hope to be helpful.

Collectives™ on Stack Overflow

Database logging in logging procedure

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related