prc_file_read_pr, prc_file_write_pr - bami74/me GitHub Wiki

create or replace procedure prc_file_read_pr(p_app_code out number,
                                             p_app_msg  out varchar2) as
  file_handle sys.utl_file.file_type; -- file handle of OS flat
  v_sql_msg   varchar2(200);
  e_not_completed exception;
  v_filename       varchar2(100);
  v_cnt            number := 0; -- Line Count
  v_line           varchar2(5000); -- Line Text
  v_complete_count number := 0; -- Insert Row Count
  v_error_count    number := 0; -- Insert Row Count
  v_msg            varchar2(100);
  v_err_col        number;

  v_erp_id     varchar2(50);
  v_mfc        varchar2(50);
  v_equip_name varchar2(50);
  v_cfg_type   varchar2(50);
  v_fa         varchar2(50);
  v_eng_type   varchar2(50);
  v_project_id varchar2(50);
  v_open_date  varchar2(50);
  v_stdate     date;
  v_allstring  varchar2(200);
begin

  -- 정상적인 데이터는 prc_list_info_tb 에 들어가게 되고
  -- 비정상적인 데이터는 ???_tb 에 들어가게 된다.

  v_stdate := sysdate;

  delete from prc_list_info_tb;

  v_stdate := sysdate;

  commit;
  ----------------------------------------------------------------------------------------------
  v_filename := 'if_list1.txt';
  --============================================================================================
  -- PCode File 을 읽어서 Table에 Insert
  --============================================================================================
  -- File Open ... Read
  file_handle      := sys.utl_file.fopen('DIR1', v_filename, 'r');
  v_error_count    := 0;
  v_complete_count := 0;

  loop
  
    -- Line Text Read
    sys.utl_file.get_line(file_handle, v_line);
  
    v_msg := 'Error  !!';
  
    v_allstring := v_line;
    if instr(v_line, '|') > 0 then
      -- 첫번째 Line은 제목 이므로 Skip 한다.
      -- 문자열중에  세부주소에 ?가 들어오는 부분에서 구분자인 | 를 잃어버려, 커럼이 하나씩 당겨지는데,
      -- 원래는 ERP 에서 정상적으로 들오와야 하지만, 보정차원에서 이를 수정하도록 함.
      select length(v_line) - length(replace(v_line, '|', ''))
        into v_cnt
        from dual;
    
      if v_cnt <> 48 then
        v_cnt  := v_cnt;
        v_line := replace(v_line, '?', '|');
      end if;
    
      v_err_col := 1;
      v_erp_id  := substr(v_line, 1, instr(v_line, '|') - 1);
    
      v_err_col := v_err_col + 1;
      v_line    := substr(v_line, instr(v_line, '|') + 1);
      v_mfc     := substr(v_line, 1, instr(v_line, '|') - 1);
    
      v_err_col    := v_err_col + 1;
      v_line       := substr(v_line, instr(v_line, '|') + 1);
      v_equip_name := substr(v_line, 1, instr(v_line, '|') - 1);
    
      v_err_col  := v_err_col + 1;
      v_line     := substr(v_line, instr(v_line, '|') + 1);
      v_cfg_type := substr(v_line, 1, instr(v_line, '|') - 1);
    
      v_err_col := v_err_col + 1;
      v_line    := substr(v_line, instr(v_line, '|') + 1);
      v_fa      := substr(v_line, 1, instr(v_line, '|') - 1);
    
      v_err_col  := v_err_col + 1;
      v_line     := substr(v_line, instr(v_line, '|') + 1);
      v_eng_type := substr(v_line, 1, instr(v_line, '|') - 1);
    
      v_err_col    := v_err_col + 1;
      v_line       := substr(v_line, instr(v_line, '|') + 1);
      v_project_id := substr(v_line, 1, instr(v_line, '|') - 1);
    
      v_err_col   := v_err_col + 1;
      v_line      := substr(v_line, instr(v_line, '|') + 1);
      v_open_date := substr(v_line, 1, instr(v_line, '|') - 1);
    
      /*      v_err_col := v_err_col + 1;
      v_line    := substr(v_line, instr(v_line, '|') + 1);
      v_empty   := v_line;*/
    
      -- Insert of Line Text
    
      begin
        insert into prc_list_info_tb
          (erp_id,
           mfc,
           equip_name,
           cfg_type,
           fa,
           eng_type,
           project_id,
           open_date)
          select v_erp_id,
                 v_mfc,
                 v_equip_name,
                 v_cfg_type,
                 v_fa,
                 v_eng_type,
                 v_project_id,
                 v_open_date
          
            from dual;
      
        v_complete_count := v_complete_count + 1;
      exception
        when others then
        
          v_sql_msg := substr(sqlerrm, 1, 200);
        
      end;
    
    elsif instr(v_line, '|') = 0 then
      -- 마지막 Line은 총 라인수이다. 해당파일의 끝이기도 하다
      exit;
    end if;
  
    -- Line Count
    v_cnt := v_cnt + 1;
    -- Line 변수 Clear
    v_line      := '';
    v_allstring := v_line;
    v_msg       := 'Complete Process !!';
  end loop;

  -- File CLose
  sys.utl_file.fclose(file_handle);

  v_stdate := sysdate;

  ----------------------------------------------------------------------------------------------

  p_app_code := 0;
  p_app_msg  := 'File Line = ' || to_char(v_cnt) || ', Complete Line = ' ||
                to_char(v_complete_count) || ' Error Line = ' ||
                to_char(v_error_count);

  commit;

exception
  when sys.utl_file.invalid_filehandle then
    rollback;
    if sys.utl_file.is_open(file_handle) then
      sys.utl_file.fclose(file_handle);
    end if;
    p_app_code := sys.utl_file.invalid_filehandle_errcode;
    p_app_msg  := 'invalid_filehandle';
  when sys.utl_file.invalid_maxlinesize then
    rollback;
    if sys.utl_file.is_open(file_handle) then
      sys.utl_file.fclose(file_handle);
    end if;
    p_app_code := sys.utl_file.invalid_maxlinesize_errcode;
    p_app_msg  := 'invalid_maxlinesize';
  when sys.utl_file.invalid_path then
    rollback;
    if sys.utl_file.is_open(file_handle) then
      sys.utl_file.fclose(file_handle);
    end if;
    p_app_code := sys.utl_file.invalid_path_errcode;
    p_app_msg  := 'invalid_path';
  when sys.utl_file.invalid_mode then
    rollback;
    if sys.utl_file.is_open(file_handle) then
      sys.utl_file.fclose(file_handle);
    end if;
    p_app_code := sys.utl_file.invalid_mode_errcode;
    p_app_msg  := 'invalid_mode';
  when sys.utl_file.read_error then
    rollback;
    if sys.utl_file.is_open(file_handle) then
      sys.utl_file.fclose(file_handle);
    end if;
    p_app_code := sys.utl_file.read_error_errcode;
    p_app_msg  := 'read_error';
  when others then
  
    if sys.utl_file.is_open(file_handle) then
      sys.utl_file.fclose(file_handle);
    
    end if;
  
    commit;
    p_app_code := 0;
    p_app_code := 0;
    p_app_msg  := v_msg || ' File Line = ' || to_char(v_cnt) ||
                  ', Complete Line = ' || to_char(v_complete_count) ||
                  ' Error Line = ' || to_char(v_error_count - 1);
    -- 에러라인수를 하나 뺀것은 첫 라인은 헤더이기 때문이다.

end prc_file_read_pr;

create or replace procedure prc_file_write_pr(p_app_code out number,
                                                      p_app_msg  out varchar2) as
  file_handle sys.utl_file.file_type; -- file handle of OS flat
  v_filename  varchar2(100);
  v_cnt       number := 0; -- Line Count
  v_line      varchar2(5000); -- Line Text
  v_row_count number := 0; -- Insert Row Count
  v_exist     number;
  v_value     varchar2(20);
  v_todate    date;
  e_not_completed exception;
  er_point  varchar2(50);
  er_row    number;
  er_msg    varchar2(1000);
  er_msg1   varchar2(1000);
  v_nowdate varchar2(8);
  v_stdate  date;

begin

  -- format : to_system_id|name|구분|운용현황|사업구분|list_id|본부||광역시도|세부주소||acc_msc|acc_bsc|acc_list|장비명|형상|위도(도)|위도(분)|위도(초)|경도(도)|경도(분)|경도(초)|개통월|관리번호|seq|CITY|TOWN|VILLAGE|년도차수
  -- to_system에 시설현황 데이터를 넘겨주기 위한 file을 만들어준다.
  -- 위의 포멧중 관리번호 다음컬럼으로 Seq 가 추가됨.
  -------------------------------------------------------------------------------------------------------

  v_stdate := sysdate;

  er_point := 'ftp file make as to_system data : list info';
  er_msg   := '현황을 만드는데 에러가 발생하였습니다.';
  ----------------------------------------------------------------------------------------------
  -- 기지국 시설현황 데이터를 만든다.
  v_filename := 'from_sytstem_to_to_system_list_info.txt';
  -- 2006.6.1 to_system 윤영식 대리의 요청에 따라
  -- 맨 뒤에 장비명과 형상을 추가하였음
  ----------------------------------------------------------------------------------------------

  file_handle := sys.utl_file.fopen('DIR1', v_filename, 'w');
  v_cnt       := 0;
  for bb in (select t.to_system_id || '|' || vw.name || '|' || decode(vw.kind, '일반', '기지국', vw.kind) || '|' ||
                     vw.to_system_use || '|' || vw.business || '|' ||
                     t.list_id || '|' || vw.area_id || '|' || '' || '|' || substr(vw.city, 1, 2) || '|' ||
                     vw.address || '|' || '' || '|' || vw.acc_msc || '|' || vw.acc_bsc || '|' ||
                     vw.acc_list || '|' || vw.equip_id || '|' || vw.cfg_type
                     || '|' || vw.latitude1 || '|' || vw.latitude2 || '|' || vw.latitude3 || '|' ||
                     vw.longitude1 || '|' || vw.longitude2 || '|' || vw.longitude3 || '|' ||
                     to_char(t.open_Date, 'YYYYMMDD') || '|' || substr(t.coms_id, 1, 13)
                     || '|' ||
                     decode(length(t.coms_id), 0, '', 13, '01', 16, substr(t.coms_id, 15, 2), '') || '|' ||
                     substr(vw.city, 1, 2) || '|' || vw.town || '|' || vw.village
                     || '|' ||
                     decode(t.year, '', '', substr(t.year, 3, 2) || '년' || to_number(t.seq) || '차')
                     || '|' || vw.hz_kind
                     || '|' || t.site_id
                     || '|' || t.project_id
                     || '|' || t.ROAD_NAME
                     || '|' || t.UNDERGROUND
                     || '|' || t.BUILDING_NUM_MAIN
                     || '|' || t.BUILDING_NUM_SUB
                     || '|' || t.BUILDING_NAME
                     || '|' || t.ADDRESS_DONG
                     || '|' || t.ADDRESS_FLOOR
                     || '|' || t.ADDRESS_HO
                     vline
               from from_sytstem_01_list_info_tb t, from_sytstem_01_to_system_info_vw vw
              where vw.info_kind = '기지국'
                and t.to_system_id = vw.to_system_id
             
             ) loop
  
    -- Line Text Read
    sys.utl_file.put_line(file_handle, bb.vline);
  
    -- Line Count
    v_cnt := v_cnt + 1;
    -- Line 변수 Clear
    v_line := '';
  
  end loop;

  select to_char(sysdate, 'YYYYMMDD') || trim(to_char(v_cnt, '000000')) into v_line from dual;

  sys.utl_file.put_line(file_handle, v_line); -- 맨 마지막에는 생성일자와 총라인수로 YYYYMMDDNNNNNN

  -- File CLose
  sys.utl_file.fclose(file_handle);
  update from_sytstem_z99_interface_tb
     set (process_date, process_time) =
          (select to_char(sysdate, 'YYYYMMDD'), to_char(sysdate, 'hh:mi:ss') from dual)
   where interface_name = 'from_sytstem_to_to_system_list_info.txt';

  commit;

  p_app_code := 0;
  p_app_msg  := 'ftp file output complete to to_system !';
exception
  when e_not_completed then
    if sys.utl_file.is_open(file_handle) then
      sys.utl_file.fclose(file_handle);
    end if;
    rollback;
  
  when others then
    rollback;
    if sys.utl_file.is_open(file_handle) then
      sys.utl_file.fclose(file_handle);
    end if;
    p_app_code := sqlcode;
    p_app_msg  := substr(sqlerrm, 1, 200);
  
end prc_file_write_pr;