CODEWITHSUNDEEP
sqloracle-databasestored-proceduresloggingplsql
Andrew Martin
Andrew Martin

Reputation: 5741

Database logging in logging procedure

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?

Upvotes: 1

Views: 57

Answers (1)

Justin Cave
Justin Cave

Reputation: 231661

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.

Upvotes: 4

Related Questions