oracle 存储过程批量生成sqlldr控制文件

最新推荐文章于 2021-04-11 23:14:15 发布

xiaoweichihuo

最新推荐文章于 2021-04-11 23:14:15 发布

阅读量516

点赞数 2

分类专栏： oracle 文章标签： sql 数据库

本文链接：https://blog.csdn.net/xiaoweichihuo/article/details/110234592

版权

oracle 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

oracle 存储过程批量生成sqlldr控制文件

由于使用oracle的sqlldr导入多个文件和表的时候，需要编辑多个控制文件，数量多的时候耗费时间较长，可以通过编写存储过程用UTL_FILE.FILE_TYPE文件包批量生成脚本
1.创建数据库目录

  create or replace directory  D1 as '/tmp/back'; --文件系统路径

2.授权用户使用权限

grant read,write on directory BLOB_FILE_DIR to gzj;--路径授权，添加对路径读、写权限
grant execute on utl_file to gzj;--utl_file包授权，添加执行权限

3.将需要导入的表明插入一张临时表做表明读取使用

4.存储过程脚本

declare  
file_ctl  varchar2(4000);
maxnum number;
vc_filename     UTL_FILE.FILE_TYPE;
own varchar2(40);
cursor table_name  is  select TABLSE_NAME from ee_t.my_table;  
begin 
   DBMS_OUTPUT.ENABLE (buffer_size=>null);
   own:='PRE_CCRS';
   for i in table_name loop
      file_ctl:=i.TABLSE_NAME||'.ctl';  ------文件名称
      dbms_output.put_line(file_ctl);
      vc_filename:= UTL_FILE.FOPEN('D1', file_ctl , 'w');   ---打开路径，文件写进内容
      UTL_FILE.PUT_LINE(vc_filename,'load'); 
      UTL_FILE.NEW_LINE(vc_filename,0);
      UTL_FILE.PUT_LINE(vc_filename,'infile /u01/backup/sdata/'||i.TABLSE_NAME||'.csv');
      UTL_FILE.NEW_LINE(vc_filename,0);
      UTL_FILE.PUT_LINE(vc_filename,'append into table  '||'ee_t.'||i.TABLSE_NAME);
      UTL_FILE.NEW_LINE(vc_filename,0);
      UTL_FILE.PUT_LINE(vc_filename,'fields terminated by  '||'"'||','||'"');
      UTL_FILE.NEW_LINE(vc_filename,0);
      UTL_FILE.PUT_LINE(vc_filename,'trailing nullcols ');
      UTL_FILE.NEW_LINE(vc_filename,0);
      UTL_FILE.PUT_LINE(vc_filename,'(');
      UTL_FILE.NEW_LINE(vc_filename,0);
      select max(COLUMN_ID) into maxnum from dba_tab_cols WHERE OWNER=own AND TABLE_NAME=i.TABLSE_NAME;--定位表最后一个字段
      FOR c IN (select COLUMN_NAME,COLUMN_ID from dba_tab_cols WHERE OWNER=own AND TABLE_NAME=i.TABLSE_NAME order by COLUMN_ID) loop
        if c.COLUMN_ID !=maxnum then
          UTL_FILE.PUT_LINE(vc_filename,c.COLUMN_NAME||',');
          UTL_FILE.NEW_LINE(vc_filename,0);
        else 
          UTL_FILE.PUT_LINE(vc_filename,c.COLUMN_NAME);
          UTL_FILE.NEW_LINE(vc_filename,0);
        end if;
      end loop;
      UTL_FILE.PUT_LINE(vc_filename,')');
      UTL_FILE.FCLOSE(vc_filename);
      end loop;
end;