如何在 pl sql 中跟踪异常终止

Question

我有一个pl sql文件，可以处理大约7000条记录。根据输入，它确实选择插入，更新到不同的表。异常处理程序编写在每个查询中。但是我的程序异常终止而不显示任何错误消息。并且这种终止每次都发生在代码的不同位置。第一次可能会处理 1200 条记录，下次可能会处理 1400 条记录。它与缓存问题有关吗？请提供输入以解决此问题。我正在使用 sql 开发人员 1.5.4。有没有办法调试这种情况？

添加 Pl sql 文件：

下面是我的 pl sql 文件，它从文件中提到的"marx_resph_bk.txt"读取输入。

===================================================================================================================================================================================================================================================

==

SET SERVEROUTPUT ON;
exec dbms_output.enable(NULL);
declare
  v_line         varchar2(500);
  v_cnt          integer;
  v_cntp          integer:=0;
  v_cnth          integer:=0;
  v_file_date    date := to_date('20140201','yyyymmdd');
  v_avail_date   date := to_date('20131210','yyyymmdd');
  v_sent_date    date;
  v_eff_date     date;
  v_status       char;
  ACCEPTED       char := 'A';
  REJECTED       char := 'R';
  v_tracking_id  kc.kc_marx_transaction.kmt_marx_tsa_sk%type;
  v_hicn  kc.kc_marx_transaction.kmt_hicn_id%type;
  v_c_id         kc.kc_marx_transaction.c_id%type;
  v_trcs         kc.kc_marx_transaction.kmt_tsa_rpy_cd%type;
  v_disp_cd      kc.kc_marx_transaction.kmt_disp_cd%type;
  v_pl_id        kc.kc_hcfa_mbr_transaction.pl_id%type;
  v_cms_ttc      kc.kc_hcfa_mbr_transaction.kcmt_type_code%type;
  v_ic_ttc       kc.kc_hcfa_mbr_transaction.kcmt_type_code%type;
  v_td_id        kc.kc_validations.td_id%type;
  v_qd_id        kc.kc_validations.qd_id%type;

  f_verbatim utl_file.file_type;
  f_errors   utl_file.file_type;
  cursor c_Trcs is
    select kcmt_reply_code trc
    from kc.kc_hcfa_mbr_transaction
    where c_id = v_c_id
    and pl_id = v_pl_id
    and kcmt_file_date = v_file_date
    and kcmt_cms_aval_dt = v_avail_date
    and kcmt_type_code = v_cms_ttc;
begin
utl_file.fclose(f_verbatim);
  utl_file.fclose(f_errors);
  f_verbatim := utl_file.fopen ('/net/iRX_downloads2/work/infocare_utl', 'marx_resph_bk.txt', 'r');
  f_errors   := utl_file.fopen('/net/iRX_downloads2/work/infocare_utl', 'marx_result.txt', 'w');
<<next_hic>>
  loop
    begin
      utl_file.get_line(f_verbatim, v_line);
      v_cntp := v_cntp+1;
      dbms_output.put_line(' Line CNT: '||v_cntp);
      exception
        when no_data_found then
        dbms_output.put_line(' No Line');
          exit;
    end;
    v_hicn := substr(v_line,1,10);
    v_status := substr(v_line,474,1);
    v_tracking_id := to_number(substr(v_line,486,15));
    begin
      dbms_output.put_line(' Selecting marx_transaction');
      select c_id, substr(kmt_ctc_id,2,4), kmt_tsa_cd, kmt_eff_dt,
        kmt_snt_to_marx_sys_dt, nvl(kmt_disp_cd,'X')
      into v_c_id, v_pl_id, v_ic_ttc, v_eff_date, v_sent_date, v_disp_cd
      from kc.kc_marx_transaction
      where kmt_marx_tsa_sk = v_tracking_id;
      if v_disp_cd <> 'X' then
        dbms_output.put_line(' Skipping');
        goto next_hic;
      end if;
      exception
        when others then
        dbms_output.put_line(' Selecting tracking Id');
          utl_file.putf(f_errors, 'Select Tracking ID :'||v_tracking_id
            ||':'||sqlerrm);
          rollback;
          return;
    end;
    if v_pl_id = '3909' then
      v_pl_id := '3963';
    end if;
    v_cms_ttc := v_ic_ttc;
    v_td_id := null;
    v_qd_id := null;
    case
      when v_ic_ttc = '51' then
v_td_id := '50'; v_qd_id := '85';
      when v_ic_ttc = '61' then
        v_td_id := '10'; v_qd_id := '20';
      when v_ic_ttc = '71' then
        v_td_id := '90'; v_qd_id := '140'; v_cms_ttc := '61';
      when v_ic_ttc = '73' then
        v_td_id := '73'; v_qd_id := '445';
      when v_ic_ttc = '75' then
        v_td_id := '72'; v_qd_id := '430';
      when v_ic_ttc in ('72','76','78','80','81') then
        null;
      else
        utl_file.putf(f_errors, 'Invalid TTC:'||v_ic_ttc||':Tracking ID :'
          ||v_tracking_id);
          dbms_output.put_line(' Invalid TTC:');
        goto next_hic;
    end case;
    if v_status = REJECTED then
      begin
        delete kc.kc_validations
        where c_id = v_c_id
        and pl_id = v_pl_id
        and td_id = v_td_id
        and ec_code like 'C%';
        exception
          when others then
            null;
      end;
    end if;
    v_cnt := 0;
    v_trcs := null;
    for l_1 in c_Trcs loop
      v_trcs := v_trcs||l_1.trc;
      v_cnt  := v_cnt + 1;
   dbms_output.put_line(' Inside Cursor loop');
      -- Only need to store validation errors for those TTCs that can be
      -- worked through the browse queue screens in the front end
      if v_status = REJECTED and v_td_id is not null then
        begin
          insert into kc.kc_validations
(c_id, pl_id, td_id, qd_id, ec_code, v_column_db_name, v_update_date)
          values
          (v_c_id, v_pl_id, v_td_id, v_qd_id, 'C'||l_1.trc, ' ', sysdate);
          exception
            when others then
            dbms_output.put_line(' Validation Insert:');
              utl_file.putf(f_errors, 'Validation Insert: c_id :'||v_c_id
                ||': pl_id :'||v_pl_Id||': td_id :'||v_td_id||': '||sqlerrm);
              rollback;
              return;
        end;
        if (l_1.trc = '127' or l_1.trc = '169') and v_ic_ttc = '61' then
          begin
             update kc.kc_customer_coverages_new
             set ccv_prj_eff_date = v_eff_date,
                 ccv_accrete_eff_date = v_sent_date,
                 ccv_ssa_medd_wth_prem_eff_dt = v_eff_date,
                 ccv_lis_eff_dt = v_eff_date,
                 ccv_update_date = sysdate,
                 ccv_update_userid = user,
                 cet_type = '4A'
             where c_id = v_c_id
             and   pl_id = v_pl_id;
             exception
               when others then
               dbms_output.put_line(' Error found CCV');
                 utl_file.putf(f_errors, 'CCV Update:c_id:'||v_c_id||':pl_id:'
                   ||v_pl_id||':eff_dt:'||to_char(v_eff_date,'yyyymmdd')||':'
                   ||'sent_dt:'||to_char(v_sent_date,'yyyymmdd')||':'||sqlerrm);
                 rollback;
                 return;
          end;
        end if;
      end if;
      -- KC.KC_MARX_TRANSACTION.KMT_TSA_RPY_CD is only large enough to
      -- accommodate a string of five TRCs (rarely, if ever, is this needed)
      if v_cnt = 5 then
        exit;
      end if;
    end loop;
begin
v_cnth := v_cnth+1;
dbms_output.put_line(' CNT: '||v_cnth);
dbms_output.put_line('v_status: '||v_status||' v_trcs: '||v_trcs||' v_tracking_id: '||v_tracking_id );
dbms_output.put_line(' v_C_ID: '||v_c_id||' v_hicn: '||v_hicn);
      update kc.kc_marx_transaction
      set kmt_disp_cd = v_status,
          kmt_tsa_rpy_cd = v_trcs,
          kmt_rsp_pcs_by_marx_sys_dt = sysdate,
          last_upd_use_id = user,
          last_upd_ts = sysdate,
          last_upd_pgm_nm = 'ACCRETION II - ACCR_ACC_REJ_STATUS.SQL'
      where kmt_marx_tsa_sk = v_tracking_id;
      exception
        when others then
        dbms_output.put_line(' Error Update Tracking');
          utl_file.putf(f_errors, 'Update Tracking ID :'||v_tracking_id
            ||':'||sqlerrm);
          rollback;
          return;
    end;
  end loop;
 dbms_output.put_line('TOTAL CNT: '||v_cnth);
  utl_file.fclose(f_verbatim);
  utl_file.fclose(f_errors);
commit;
end;
/

=========================================================================================================================================================================================================================================

===

而且它永远不会达到可以显示我总 CNT 的最终 dbms_output.put_line 的程度。

Answer 1

你的异常处理程序

when others then
dbms_output.put_line(' Selecting tracking Id');
  utl_file.putf(f_errors, 'Select Tracking ID :'||v_tracking_id
    ||':'||sqlerrm);
  rollback;
  return;

包含错误跟踪写入文件 - 具有相同信息的 dbms_output.put_line（） 怎么样？您可以将其作为假脱机文件输出。似乎你也使用循环 - 结束循环;和 goto 控制方法 - 你能使用 while（） 循环代替吗？它可能更跨性别。

Ald 我猜你也有 RETURN 语句的问题 - 因为你在嵌套块中返回之前没有关闭文件处理程序，所以你没有关于错误的信息，文件是空的，你没有到达主块的末尾：

SQL> declare
  2  
  3    f_errors   utl_file.file_type;
  4  
  5  begin
  6    utl_file.fclose(f_errors);
  7    f_errors   := utl_file.fopen('FDC_BTI_DIR', 'test_01.txt', 'w');
  8      begin
  9  
 10        raise no_data_found;
 11  
 12        exception
 13          when others then
 14          dbms_output.put_line(' Selecting tracking Id');
 15            utl_file.putf(f_errors, 'Select Tracking ID :'||sqlerrm);
 16            rollback;
 17            return;
 18      end;
 19    utl_file.fclose(f_errors);
 20  end;
 21  /
Selecting tracking Id                                                           
SQL> declare
  2  
  3    f_errors   utl_file.file_type;
  4    buff varchar2(4000);
  5  
  6  begin
  7    utl_file.fclose(f_errors);
  8    f_errors   := utl_file.fopen('FDC_BTI_DIR', 'test_01.txt', 'r');
  9    utl_file.get_line(f_errors, buff, 4000);
 10    dbms_output.put_line(buff);
 11    utl_file.fclose(f_errors);
 12  end;
 13  /
declare
*
error in line 1:
ORA-01403: data not found 
ORA-06512: at  "SYS.UTL_FILE", line 106 
ORA-06512: at  "SYS.UTL_FILE", line 746 
ORA-06512: at  line 9 

SQL> declare
  2  
  3    f_errors   utl_file.file_type;
  4  
  5  begin
  6    utl_file.fclose(f_errors);
  7    f_errors   := utl_file.fopen('FDC_BTI_DIR', 'test_01.txt', 'w');
  8      begin
  9  
 10        raise too_many_rows;
 11  
 12        exception
 13          when others then
 14          dbms_output.put_line(' Selecting tracking Id');
 15            utl_file.putf(f_errors, 'Select Tracking ID :'||sqlerrm);
 16            utl_file.fclose(f_errors);
 17            rollback;
 18            return;
 19      end;
 20    utl_file.fclose(f_errors);
 21  end;
 22  /
Selecting tracking Id                                                           
SQL> declare
  2  
  3    f_errors   utl_file.file_type;
  4    buff varchar2(4000);
  5  
  6  begin
  7    utl_file.fclose(f_errors);
  8    f_errors   := utl_file.fopen('FDC_BTI_DIR', 'test_01.txt', 'r');
  9    utl_file.get_line(f_errors, buff, 4000);
 10    dbms_output.put_line(buff);
 11    utl_file.fclose(f_errors);
 12  end;
 13  /
Select Tracking ID :ORA-01422: exact fetch returns more than requested number of rows                                                                    
SQL> declare
  2  
  3    f_errors   utl_file.file_type;
  4  
  5  begin
  6    utl_file.fclose(f_errors);
  7    f_errors   := utl_file.fopen('FDC_BTI_DIR', 'test_01.txt', 'w');
  8      begin
  9  
 10        raise invalid_number;
 11  
 12        exception
 13          when others then
 14          dbms_output.put_line(' Selecting tracking Id');
 15            utl_file.putf(f_errors, 'Select Tracking ID :'||sqlerrm);
 16            rollback;
 17      end;
 18    utl_file.fclose(f_errors);
 19  end;
 20  /
Selecting tracking Id                                                           
SQL> declare
  2  
  3    f_errors   utl_file.file_type;
  4    buff varchar2(4000);
  5  
  6  begin
  7    utl_file.fclose(f_errors);
  8    f_errors   := utl_file.fopen('FDC_BTI_DIR', 'test_01.txt', 'r');
  9    utl_file.get_line(f_errors, buff, 4000);
 10    dbms_output.put_line(buff);
 11    utl_file.fclose(f_errors);
 12  end;
 13  /
Select Tracking ID :ORA-01722: invalid_number