Oracle Log Procedure

In example below is shown how to create oracle log procedure and how to call procedure from the main procedure or function.

Note line of code  PRAGMA AUTONOMOUS_TRANSACTION in oracle log procedure (oracle_log_procedure). Procedure with this line of code can do operations COMMIT and ROLLBACK without influence on data in the main procedure or function.

EXAMPLE:

1. Create table CC_LOG:

CREATE TABLE CC_LOG
(  LOG_ID    NUMBER, 
   LOG_DATE  DATE, 
   LOG_TEXT  VARCHAR2(500)
);

2. Create sequence CC_LOG_SEQ:

CREATE SEQUENCE CC_LOG_SEQ
  START WITH 1
  MAXVALUE 999999999999999999999999999
  MINVALUE 1
  NOCYCLE
  NOCACHE
  NOORDER;

3. Create oracle log procedure:

CREATE PROCEDURE oracle_log_procedure(p_text IN VARCHAR2)   
IS     
PRAGMA AUTONOMOUS_TRANSACTION;     
BEGIN     
     INSERT INTO cc_log(log_id, log_date, log_text)       
                VALUES (cc_log_seq.NEXTVAL, SYSDATE, p_text);
     COMMIT;   
END oracle_log_procedure;

4. How to call oracle log procedure:

.
.
.
COMMIT;     
oracle_log_procedure('UPDATE RECORD complete. '||v_counter||' records');      
 EXCEPTION 
   WHEN OTHERS THEN
       oracle_log_procedure('UPDATE RECORD failed: '||SQLERRM);        
       oracle_log_procedure('UPDATE RECORD failed: '||SQLCODE);        
END update_record;