Amit February 2016

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;

Answers


Aleksej February 2016

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;

Post Status

Asked in February 2016
Viewed 1,511 times
Voted 4
Answered 1 times

Search




Leave an answer