Re: problem with exception handling

I do use autonomous transactions to log error messages:

CREATE OR REPLACE PROCEDURE SD_LOG_SQL_ERROR(SQLCODE IN NUMBER, SQLERRM IN VARCHAR2, BLOCK_NAME IN VARCHAR2, PARAM_VALUES IN VARCHAR2) AS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO SD_SQL_ERROR_LOG(ERROR_TIME, SQLCODE, SQLERRM, BLOCK_NAME, PARAM_VALUES)
VALUES(SYSDATE, SQLCODE, SQLERRM, BLOCK_NAME, PARAM_VALUES); COMMIT;
END
;

...

EXCEPTION
WHEN OTHERS THEN
SD_LOG_SQL_ERROR(SQLCODE, SQLERRM, 'My_SP_Name', TRIM(My_SP_parameters));
ROLLBACK;
RAISE; It works all right and it is not my concern. It is the DML in the body of the procedure, not in the exception handler:

INSERT INTO AAT VALUES(P_N); that is a part of ongoing transaction and somehow gets rolled back. This DML does not belong to an autonomous transaction. Received on Thu Dec 16 2004 - 10:51:20 CST