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;