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)
);

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;

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;

CREATE PROCEDURE oracle_log_procedure(p_text IN VARCHAR2)

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;

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;

TOOLS

POSTS