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:
|
1 2 3 4 5 |
CREATE TABLE CC_LOG ( LOG_ID NUMBER, LOG_DATE DATE, LOG_TEXT VARCHAR2(500) ); |
2. Create sequence CC_LOG_SEQ:
|
1 2 3 4 5 6 7 |
CREATE SEQUENCE CC_LOG_SEQ START WITH 1 MAXVALUE 999999999999999999999999999 MINVALUE 1 NOCYCLE NOCACHE NOORDER; |
3. Create oracle log procedure:
|
1 2 3 4 5 6 7 8 |
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:
|
1 2 3 4 5 6 7 8 9 10 |
. . . 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; |