0

My proc is like this

CREATE OR REPLACE PROCEDURE TEST_INSERT
    IS
      TotalUpd pls_integer;
      err_code number;
      err_msg varchar(500);

      invalid_excep EXCEPTION;
      PRAGMA EXCEPTION_INIT(invalid_excep,-904);  
    BEGIN
      insert into test_proc_tbl(un_id) /* this column doesn't exist */
        select 'asd' from dual;

        TotalUpd := SQL%ROWCOUNT;
        commit;

    EXCEPTION

        WHEN invalid_excep THEN
            INSERT INTO err_log(src,typ_ex,ERROR_DESC,ROW_COUNT,RUN_STATUS,UPDATED_DATE)
            select 'TEST','STORE PROCEDURE',to_char(err_code) || '-' || err_msg,TotalUpd,'ERROR',SYSDATE from dual;   
            COMMIT;

        WHEN OTHERS THEN 
            err_code := SQLCODE;
            err_msg := SUBSTR(SQLERRM,1,200);

            INSERT INTO err_log(src,typ_ex,ERROR_DESC,ROW_COUNT,RUN_STATUS,UPDATED_DATE)
            select 'TEST','STORE PROCEDURE',to_char(err_code) || '-' || err_msg,TotalUpd,'ERROR',SYSDATE from dual;  
            COMMIT;   

    END; 

I am trying to insert Invalid identifier error in my log table, purpose of doing this some of my base table will be recreated twice in a week, other team can miss or modify column name. Here my problem is Exception doesn't catch invalid identifier error, how to fix this?

1 Answer 1

2

The way you wrote your procedure, ORA-00904 is a compile time error, you can not catch that with a runtime exception handler.

Simplest workaround is to use dynamic SQL. Instead of:

 insert into test_proc_tbl(un_id) /* this column doesn't exist */
        select 'asd' from dual;

Use this:

execute immediate ' insert into test_proc_tbl(un_id) select ''asd'' from dual';

This will compile regardless of the existence of un_id column, and you can catch the error. However, you will lose dependecy tracking between the table and your procedure.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.