Hi,
We use a procedure routine with autonomous transaction block for error logging in our application(java app deployed on websphere)
Recently we had an issue where existing log information was not sufficient to identify exact cause of error.
So we added few more calls to error logging routine and deployed a debug code.
But when the issue was reproduced nothing got logged in error logging tables.
Although actual ORA error was captured in application(websphere) logs which made us wonder why was problem in pl/sql code for error logging.
This has happened few times earlier also but very rarely but we are not sure what could be cause of such random behavior of autonomous transaction routine.
Please if anyone has an idea of reason or encountered such problem , please share.
The routine we are using as very simple code as follows:
PROCEDURE log_error_msg_proc (
pi_debug_msg IN error_log_tab.log_message%TYPE,
)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO error_log_tab
(ID,
log_message,
created_dt
)
VALUES (log_sq.NEXTVAL, ,
pi_debug_msg,
SYSTIMESTAMP
);
COMMIT;
END IF;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END log_error_msg_proc;