解决存储过程截取错误的问题

今天遇到一个问题 下发的存储过程有时候会出现 "无效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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12216142/viewspace-492377/