oracle数据库读取操作系统的物理文件-转载，待完善

--源地址不详

 --创建目录
SQL> create directory dir_xls as '/home/oracle';

Directory created.

--给用户授权
SQL> grant all on directory dir_xls to public;

Grant succeeded.

 

 

 

CREATE OR REPLACE PROCEDURE p_xlstooracle IS
   v_file                utl_file.file_type;
   out_v                 VARCHAR2(4000);
   v_id                  varchar2(20);
   v_service_competition varchar2(20);
   v_cu_market_share     varchar2(20);
   v_ct_market_share     varchar2(20);
   v_cm_market_share     varchar2(20);
   v_other_market_share  varchar2(20);
BEGIN

--判断文件是否被打开
   IF utl_file.is_open(v_file) THEN
      utl_file.fclose(v_file);
   END IF;
   v_file := utl_file.fopen('DIR_XLS', 'xls_to_oracle.txt', 'r');

--循环读取文件内容，赋值 out_v
   LOOP
      BEGIN
         utl_file.get_line(v_file, out_v);
      EXCEPTION
         WHEN no_data_found THEN
            EXIT;
      END;

--依次读取空格的位置  赋值 v_id
      v_id   := substr(out_v, 1, instr(out_v, ' ', 1, 1) - 1);

--读取第二个字段的内容      
      v_service_competition := substr(out_v,
                       instr(out_v, ' ', 1, 1) + 1,
                       instr(out_v, ' ', 1, 2) - instr(out_v, ' ', 1, 1)-1);
                       
--读取第三个字段的内容                       
      v_cu_market_share := substr(out_v,
                       instr(out_v, ' ', 1, 2) + 1,
                       instr(out_v, ' ', 1, 3) - instr(out_v, ' ', 1, 2)-1);
                       
--读取第四个字段的内容                       
      v_ct_market_share := substr(out_v,
                       instr(out_v, ' ', 1, 3) + 1,
                       instr(out_v, ' ', 1, 4) - instr(out_v, ' ', 1, 3)-1);
                       
--读取第五个字段的内容                       
      v_cm_market_share := substr(out_v,
                       instr(out_v, ' ', 1, 4) + 1,
                       instr(out_v, ' ', 1, 5) - instr(out_v, ' ', 1, 4)-1);
                       
--读取地六个字段的内容                       
      v_other_market_share := substr(out_v,
                       instr(out_v, ' ', 1, 5) + 1,
                       length(out_v) - instr(out_v, ' ', 1, 5));
                       
--插入 表  i_exch_info    
insert into     i_exch_info values(v_id,v_service_competition,v_cu_market_share,v_ct_market_share,v_cm_market_share,v_other_market_share);             

      dbms_output.put_line(out_v);
   END LOOP;
   
--关闭文件   
   utl_file.fclose(v_file);
   
   
END p_xlstooracle;