utl_file应用

remove时ORA-29283: 文件操作无效

其原因是文件是由ftp上传的 而vsftpd.conf中有个参数local_umask位置为022

导致其上传的文件g没有w权限 修改后即可解决 

begin
  --dbms_output.disable;
  --dbms_output.enable(999999999999);
  --need Grant READ,WRITE on Directory ORA_DIRECTORY to user
  /**
  * @description   程序日志开始
  * @field-mapping vv_task_pos = ('程序开始日志')
  * @call bassys#p_sys_log
  */
  vv_task_pos  := '程序开始日志';
  vv_task_name := 'pm_trans_data_to_file';

  p_sys_log(vi_task_id,
                   vv_task_name,
                   null,
                   iv_batchnum,
                   1,
                   vv_err_msg,
                   vv_task_pos,
                   vi_result);
  vv_yesterday := to_char(to_date(substr(iv_batchnum,1,8),'yyyymmdd')-1,'yyyymmdd');
     /**
       *  @description 校验时间精度
       */
     if length(iv_batchnum) <> 11 then
      vv_err_msg  := '数字或值错误 超出限定精度';
      raise exc_return;
      end if;
    /**
      *  @description 生成文件名
      */
      vv_task_pos := '生成文件名';
      vv_file_name :='TMRD'||iv_batchnum||'.571';
      dbms_output.put_line('文件名:'||vv_file_name);

    /**
      * @descripton 判断源数据是否存在
      */
      vv_task_pos := '判断源数据是否存在';
      select count(1)
        into vv_com_flag
        from tm_remind_deduct_fee_d
       where flag = 0
         and statis_date = vv_yesterday
         and rownum = 1;
    /**
      * @descripton  判断当前批次是否处理
      */
      vv_task_pos := '判断当前批次是否处理';
      select count(1)
        into vv_batch_flag
        from tm_remind_deduct_fee_d t
       where batch_record = iv_batchnum
         and rownum = 1;

     /**
       * @description 判断文件是否存在
       */
     vv_task_pos := '判断文件是否存在';
     utl_file.fgetattr(vv_file_path,vv_file_name,vb_is_exist,vi_file_size,vi_file_block);
     if vb_is_exist then
     null;
     else
     vv_err_msg := '文件不存在';
     dbms_output.put_line(vv_err_msg);
     raise exc_return;
     end if;
     /**
       * @description  批次转时间
       */

      ----------------------------------------------------
      /*vv_batch_flag :=  lpad(trunc((substr(iv_date, 9, 2) * 60 +
                        substr(iv_date, 11, 2)) / 15, 0), 3, 0);
      vv_batch:= substr(iv_date, 1, 8) || vv_batch_flag;
      --dbms_output.put_line();
      if vv_batch is null or length(vv_batch) <> 11 then
      vv_err_msg  := '批次异常';
      raise exc_return;
      end if;*/

     /*
      * @description 判断新文件是否存在
      */

       vv_file_name_n :='MRD'||iv_batchnum||'.571';
       dbms_output.put_line('输出文件名:'||vv_file_name_n);

       vv_task_pos := '判断新文件是否存在';
       utl_file.fgetattr(vv_file_path,vv_file_name_n,vb_is_exist,vi_file_size,vi_file_block);
       --如果存在 删除
       if vb_is_exist then
             utl_file.fremove(vv_file_path,vv_file_name_n);
       end if;
     /*
      * @description 创建输出文件
      */
       vv_task_pos := '创建输出文件';
       file_handle_n := utl_file.fopen(vv_file_path,vv_file_name_n, 'w');
       utl_file.put_line(file_handle_n,
       '1046000571'||lpad(' ',20,' ')||rpad('46000000',10,' ')||
       rpad(substr(iv_batchnum,9,3),4,' ')||lpad(' ',20,' ')||substr(iv_batchnum,1,8)||lpad(trunc(substr(iv_batchnum, 9, 3) * 15 / 60, 0), 2, '0') ||
       lpad(mod(substr(iv_batchnum, 9, 3) * 15, 60), 2, '0')||'00'||
       '01'||lpad(' ',228,' ')||chr(13)
       );
       utl_file.fclose(file_handle_n);
       dbms_output.put_line('完成创建输出文件名:'||vv_file_name_n);

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

 

      /**
      * @description 遍历文件 获取文件行数
      * @description 同时把行记录插入新文件
      */
      vv_task_pos := '遍历文件 获取文件行数';

      file_handle := utl_file.fopen(vv_file_path, vv_file_name, 'r');
      file_handle_n := utl_file.fopen(vv_file_path, vv_file_name_n, 'a');
      --utl_file.new_line(file_handle_n); --跳行
      loop
        begin
          utl_file.get_line(file_handle, vv_return);
          utl_file.put_line(file_handle_n, vv_return);--||chr(13)
          vv_fee_total := vv_fee_total + to_number(substr(vv_return,140,6));
          vv_fee_dicount := vv_fee_dicount + to_number(substr(vv_return,146,6));
          --dbms_output.put_line('小记信息费：'||substr(vv_return,145,12));
          vi_count := vi_count + 1;
        exception
          when NO_DATA_FOUND then
            exit;
        end;
      end loop;
      --dbms_output.put_line('xx信息费：'||vv_fee_total);
      dbms_output.put_line('旧文件行数：'||vi_count);
      dbms_output.put_line('a信息费：'||vv_fee_total);
      dbms_output.put_line('b信息费：'||vv_fee_dicount);
      utl_file.fclose(file_handle);
      utl_file.fclose(file_handle_n);

      /**
        * @description 抽取数据补齐50000
        */
        vv_task_pos := '抽取数据补齐50000';
        file_handle := utl_file.fopen(vv_file_path, vv_file_name_n, 'a');
        --utl_file.new_line(file_handle); --跳行
        if vv_batch_flag = 1 then--重新装载旧批次数据  不限定时段
        dbms_output.put_line('重新装载旧批次数据：'||iv_batchnum);
         for cur in (select nvl(rec_type, '  ') || lpad(nvl(msg_id, 0), 21, 0) ||
                            rpad(nvl(chrg_dn, ' '), 15, ' ') || rpad(' ', 15, ' ') ||
                            rpad(nvl(third_dn, ' '), 15, ' ') || ' 00' ||
                            rpad(nvl(chap_id, '0'), 6, ' ') || '99' ||
                            rpad(nvl(content_id, '0'), 12, ' ') || nvl(sale_mode, '00') ||
                            '100020' || rpad(nvl(sp_code, ' '), 20, ' ') ||
                            rpad(nvl(oper_code, ' '), 20, ' ') ||
                            lpad(nvl(discount_fee, 0), 6, 0) ||
                            lpad(nvl(discount_fee, 0), 6, 0) ||rpad(' ', 4, ' ')|| '00'
                            ||rpad(nvl(ip_addr, ' '), 15, ' ') || rpad(0, 15, ' ') ||
                            finish_time || finish_time ||
                            rpad(nvl(channel_id, ' '), 8, ' ')  || rpad(' ', 85, ' ')  as strbuf,discount_fee,
                           rowid
                      from tm_remind_deduct_fee_d
                     where batch_record = iv_batchnum)loop
               vi_count := vi_count + 1;
               vv_fee_total := vv_fee_total + cur.discount_fee;
               vv_fee_dicount := vv_fee_dicount + cur.discount_fee;
               utl_file.put_line(file_handle, cur.strbuf||chr(13));
               vv_err_msg := cur.rowid;
        end loop;
        else
             if iv_max_size - vi_count > 0
                 and trunc((to_number(substr(iv_batchnum,9,3))*15+15)/60,0)>= iv_timing --批次转换时间
                 and vv_com_flag = 1 then
                dbms_output.put_line('新批次：'||iv_batchnum);
                vv_account_time := to_char(sysdate,'yyyymmddhh24miss');
                --a  append 附加数据到文件尾部
                -- 需当前用户对文件写权限
               for cur in (select nvl(rec_type, '  ') || lpad(nvl(msg_id, 0), 21, 0) ||
                                  rpad(nvl(chrg_dn, ' '), 15, ' ') || rpad(' ', 15, ' ') ||
                                  rpad(nvl(third_dn, ' '), 15, ' ') || ' 00' ||
                                  rpad(nvl(chap_id, '0'), 6, ' ') || '99' ||
                                  rpad(nvl(content_id, '0'), 12, ' ') || nvl(sale_mode, '00') ||
                                  '100020' || rpad(nvl(sp_code, ' '), 20, ' ') ||
                                  rpad(nvl(oper_code, ' '), 20, ' ') ||
                                  lpad(nvl(discount_fee, 0), 6, 0) ||
                                  lpad(nvl(discount_fee, 0), 6, 0) ||rpad(' ', 4, ' ')|| '00'
                                  ||rpad(nvl(ip_addr, ' '), 15, ' ') || rpad(0, 15, ' ') ||
                                  finish_time || finish_time ||
                                  rpad(nvl(channel_id, ' '), 8, ' ')  || rpad(' ', 85, ' ')   as strbuf,discount_fee,
                                   rowid
                              from (select *
                                      from tm_remind_deduct_fee_d
                                     where flag = 0
                                       and statis_date = vv_yesterday
                                     order by finish_time)
                             where rownum <= (iv_max_size - vi_count)) loop
                  vi_count := vi_count + 1;
                  vv_fee_total := vv_fee_total + cur.discount_fee;
                  vv_fee_dicount := vv_fee_dicount + cur.discount_fee;
                  begin
                    update tm_remind_deduct_fee_d
                       set flag = 1 ,
                           batch_record = iv_batchnum,
                           accounttime = vv_account_time
                     where rowid = cur.rowid;
                    utl_file.put_line(file_handle, cur.strbuf||chr(13));
                    vv_err_msg := cur.rowid;
                  end;
                commit;
              end loop;
            end if;
        end if;

       dbms_output.put_line('新文件行数：'||vi_count);
       dbms_output.put_line('a信息费：'||vv_fee_total);
       dbms_output.put_line('b信息费：'||vv_fee_dicount);
       utl_file.put_line(file_handle,
       '90'||rpad('46000000',10,' ')||rpad(' ',10,' ')||'46000571'||rpad(substr(iv_batchnum,9,3),4,
    ' ')
       ||rpad(' ',10,' ')||substr(iv_batchnum,1,8)||lpad(trunc(substr(iv_batchnum, 9, 3) * 15 / 60, 0), 2, '0') ||
       lpad(mod(substr(iv_batchnum, 9, 3) * 15, 60), 2, '0')||'00'||lpad(vi_count,9,0)||lpad
    (vv_fee_total,10,0)
       ||lpad(vv_fee_dicount,10,0)||rpad(' ',221,' ')||chr(13));
      utl_file.fclose(file_handle);
      vv_task_pos := '程序结束日志';
      --dbms_output.put_line(to_number(substr(iv_date,9,2)));
      if  trunc((to_number(substr(iv_batchnum,9,3))*15+15)/60,0) >= iv_timing and
          vv_com_flag = 1 then
      p_sys_log(vi_task_id, null, null, null, -1, '新增', null, iv_max_size - vi_count);
        oi_return := 0;
      else
      p_sys_log(vi_task_id, null, null, null, -1, '源表无数据', null, 0);
       dbms_output.put_line('源表无数据');
        oi_return := 0;
  end if;
  /**
    * @description 即时删除临时文件
    */
       vv_task_pos := '即时删除临时文件';
      /* dbms_output.put_line('即时删除文件名:'||vv_file_name);
       utl_file.fgetattr(vv_file_path,vv_file_name,vb_is_exist,vi_file_size,vi_file_block);
       --如果存在 删除
       if vb_is_exist then
             utl_file.fremove(vv_file_path,vv_file_name);
       end if;*/
  /**
    * @description 定时处理合并文件
    */
    vv_task_pos := '定时处理合并文件';
   /* if substr(iv_batchnum,9,3) = '095' then
    vv_last_threeday :=  to_char(to_date(substr(iv_batchnum,1,8),'yyyymmdd')-2,'yyyymmdd');
        for i in 0..95 loop
          dbms_output.put_line('删除文件名:MRD'||'MRD'||vv_last_threeday||lpad(i,3,0)||'.571');
          utl_file.fgetattr(vv_file_path,'MRD'||vv_last_threeday||lpad(i,3,0)||'.571',vb_is_exist,vi_file_size,vi_file_block);
       --如果存在 删除
           if vb_is_exist then
                 utl_file.fremove(vv_file_path,'MRD'||vv_last_threeday||lpad(i,3,0)||'.571');
                 dbms_output.put_line('SUCCESS');
           end if;
        end loop;
    else
    dbms_output.put_line('测试'||substr(iv_batchnum,9,3));
    end if;*/
 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/21993926/viewspace-672393/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/21993926/viewspace-672393/