The AUTONOMOUS_TRANSACTION pragma changes the way a subprogram works within a transaction. A subprogram marked with this pragma can do SQL operations and commit or roll back those operations, without committing or rolling back the data in the main transaction

e.g.

PROCEDURE TM_RB_PROC_LOG(

P_CORR_ID IN VARCHAR2,

P_SCREEN IN VARCHAR2,

REQIN IN CLOB,

REQOUT IN CLOB,

P_RESULT_CODE IN VARCHAR2)

IS

PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

INSERT INTO TM_RB_TRAN_UTL_LOG(REF_NO, PROC_TYPE, XML_REQUEST, XML_RESPONSE, RESULT_CODE, LOG_DATE)

VALUES(P_CORR_ID, P_SCREEN, XMLTYPE(REQIN), XMLTYPE(REQOUT), P_RESULT_CODE, SYSDATE);

COMMIT;

EXCEPTION

WHEN OTHERS THEN RAISE;

END TM_RB_PROC_LOG;

Advertisements