Using Autonomous Transactions in PL SQL to Update a Debug Log

Here's a simple PL SQL function and accompanying table definition that can be used for writing to a DEBUG table. This is very useful when you're debugging packages (perhaps called from APEX?) and you cannot write to the console easily.

PROCEDURE DEBUG_WRITE(P_MSG IN VARCHAR2, P_PROC IN VARCHAR2)
IS
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
   BEGIN
        INSERT INTO ES2_DEBUG_LOG (MSG_TIME, PROC_NAME, MSG)
        VALUES(SYSTIMESTAMP, P_PROC, P_MSG);
        COMMIT;        
   -- EXCEPTION      
   END;
END;

The table to hold the data looks like this

CREATE TABLE SAMPLE.ES2_DEBUG_LOG
(
  MSG_TIME   TIMESTAMP(6),
  MSG        VARCHAR2(1000 BYTE),
  PROC_NAME  VARCHAR2(40 BYTE)
)
TABLESPACE SAMPLE_DATA
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

An example of writing to the log looks like this:

  DEBUG_WRITE('1','create es2');
    SELECT es2_experiment_id.NEXTVAL
    INTO l_experiment_id
    FROM dual;
    
    DEBUG_WRITE('2','create es2');
    
    p_experiment_id := l_experiment_id;
    
    SELECT status_id INTO l_status_id
    FROM es2_status_table
    WHERE status_desc = 'New';
    
    DEBUG_WRITE('3','create es2');

Here's the output from the log after one run