Andrew Andrew - 1 month ago 8
SQL Question

Oracle not able to insert exception into table

I have below procedure where i am trying to track the exceptions into

I_Log
table.To test whether its working or not I have made a
ORA-00933: SQL command not properly ended
error in my query where I am trying to insert into
I_OPTION
table. When i run this procedure the dbms output line is printing the error below but its not getting inserted into
I_Log
table:

OTHERS exception in EXT_I_OPTION - ID:1000196-933----ORA-00933: SQL command not properly ended


Below is my procedure:

CREATE OR REPLACE PROCEDURE
"EXT_I_OPTION"(in_id IN NUMBER DEFAULT 0)
AS
err_code VARCHAR(100);
err_msg VARCHAR(100);
in_event_id NUMBER;
in_db_link VARCHAR2(50);
in_env_id NUMBER;
l_sql VARCHAR2(5000);
l_sql1 VARCHAR2(5000);
BEGIN

FOR I_row IN I_cur
LOOP

l_sql2 := INSERT INTO I_OPTION(ID)
select DISTINCT(SO.ID)
)
from Icard I;

END LOOP;

EXCEPTION WHEN OTHERS THEN
err_code := SQLCODE;
err_msg := SUBSTR(SQLERRM, 1, 200);
INSERT INTO I_log (I_ID)
VALUES (i_id);

RAISE;

COMMIT;

END ext_I_option;

Answer

It seems that you have a RAISE before COMMIT; this way, the error will be raised before doing COMMIT, so you don't find data in your log table.

According to suggestions, you should define a procedure to handle your log table:

CREATE OR REPLACE procedure i_LOG (...) AS
  PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN
    Insert into I_LOG(...); 

    COMMIT;
END;
/

This procedure runs in a separate transaction, so it only does commit of log data, with no conflict with data you modify in your main procedure. Then you should modify your error handling in this way, avoiding COMMIT statement, that can be really dangerous, saving partial, unconsistent data:

DBMS_OUTPUT.PUT_LINE('OTHERS exception in EXT_I_OPTION - ID:'||to_char(ID) || err_code || '----' || err_msg );
ins_I_LOG(...);
RAISE;
Comments