解決儲存過程擷取錯誤的問題

今天遇到一個問題 下發的儲存過程有時候會出現 "無效rowid"或者"物件不再存在"的問題,而一旦發生這種錯誤,則儲存過程當中的  EXCEPTION 無法擷取到錯誤.過程指令碼如下:

CREATE OR REPLACE PROCEDURE SP_TEST
 AUTHID CURRENT_USER
 AS
  T_log_id NUMBER(20);     
  T_err_msg VARCHAR(100);  
 BEGIN
  BEGIN

    SELECT seq_run_log.NEXTVAL INTO T_log_id from dual;
execute immediate 'insert into log_table (row_id,start_date,table_name) values (:1,sysdate,''TEST_ZYP'')'
using T_log_id;
commit;
--巢狀儲存過程 判斷TEST_ZYP表是否存在,存在則丟棄.
drop_temp_table('TEST_ZYP');
execute immediate '
        create table TEST_ZYP nologging as select * from test_zyp@dblink_etl';

commit;
---寫入結束日誌
 execute immediate 'update log_table set row_num=(select count(*) from TEST_ZYP) where row_id=:1'

  Using T_log_id;
  commit;
      EXCEPTION
        WHEN OTHERS
         THEN
             T_err_msg := SQLERRM;
  Execute Immediate '
    update log_table Set END_DATE=SYSDATE,STATE=''2'',ERROR_DEPICT=:1 WHERE ROW_ID=:2'
  using T_err_msg,T_log_id;
commit;

END SP_TEST;

問題就出在結束日誌這裡,因為最後更新日誌表log_table的時候,由於表本身出現了錯誤,因此導致整個update語句報錯,並且丟擲錯誤,從而在每天的例行檢查當中無法發現到底出現了什麼錯誤,同時也不好向局方說明情況.因此作出瞭如下改動.

CREATE OR REPLACE PROCEDURE SP_TEST
 AUTHID CURRENT_USER
 AS
  T_log_id NUMBER(20);
  T_err_msg VARCHAR(100);
  t_tab_ext NUMBER(1);
 BEGIN
  BEGIN
    SELECT seq_run_log.NEXTVAL INTO T_log_id from dual;
execute immediate 'insert into log_table (row_id,start_date,table_name) values (:1,sysdate,''TEST_ZYP'')'
using T_log_id;
commit;

drop_temp_table('TEST_ZYP');
execute immediate '
        create table TEST_ZYP nologging tablespace REPORT as select * from test_zyp@dblink_etl';
COMMIT;
execute immediate '
 analyze table TEST_ZYP validate structure';
---寫入結束日誌
  execute immediate 'update log_table Set END_DATE=SYSDATE,STATE=''1''
                     WHERE ROW_ID=:1'
  Using T_log_id;
  commit;
      EXCEPTION
        WHEN OTHERS
         THEN
             T_err_msg := SQLERRM;
  execute immediate '
    update log_table Set END_DATE=SYSDATE,STATE=''2'',ERROR_DEPICT=:1 WHERE ROW_ID=:2'
  using T_err_msg,T_log_id;
commit;
end;
 begin
  select count(1) into t_tab_ext from log_table where state='1' and row_id=''||T_log_id||'';
 end;
  if t_tab_ext =1 then
  begin
    execute immediate 'update log_table set row_num=(select count(*) from TEST_ZYP) where row_id=:1'
  using T_log_id;
  commit;
  end;
  end if;

END SP_TEST;
由此就達到了擷取錯誤的目的,同時需要說明的是 number型的變數在引用的時候必須要用兩個引號 例如 ''||T_log_id||'' 否則會被作為varchar型變數而出錯;同時給t_tab_ext變數賦值 還必須是要在EXCEPTION  end之後 否則不會執行賦值的操作.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12216142/viewspace-492377/，如需轉載，請註明出處，否則將追究法律責任。