25 March 2015

Generate SQL*Loader control files for a schema

Someday, you may need to load all tables with SQL*Loader. That can be in a migration situation or similar situations. It can be a tedious task to create control files for SQL*Loader. For this, I created the following setup, that gives you the basic control files.

It's based on Linux. You can make adjustments as you wish. Always check your requirements.


This method, builds a table with the contents of the sqlloader controlfiles.
It has three columns  (s1,s2,s3) that contain owner,table_name and sort.

These columns are used for getting the output in the correct order.
When the table is build, you create a second SQL from it, that will run and spool all separate controlfiles.

Things you might want to change are:
  • the owner of the schema  (now APPOWNER)
  • the location where the controlfiles ares spooled to (now /tmp/work)
  • the csv delimiter (now a caret symbol ^)


There are five selects in the create table:
  • spool /tmp/work/table_name.ctl   (lines with s3= -999 )
  • -- controlfile: table_name.ctl   (s3= -99 )
  • load data infile ... into table ...   etc.  (s3= 0 )
  • the columns part, separated by commas and a ) for the last column ( s3 = column_id)
  • spool off   ( s3 = 999 )
The s3 column is very easy to get the output sorted.
I'd like to hear if you can use this script :-)

drop table system.tool_cr_ctl;

create table system.tool_cr_ctl as
select owner s1,table_name s2,-999 s3,'spool /tmp/work/'
       ||lower(table_name)||'.ctl'  SOURCE
  from dba_tables
  where owner like 'APPOWNER'
union
select owner s1,table_name s2,-99 s3,'prompt-- Controlfile:   '
       ||lower(table_name)||'.ctl'  SOURCE
  from dba_tables
  where owner like 'APPOWNER'
union
select owner s1,table_name s2,0 s3,'prompt load data infile '
      ||chr(39)||'/tmp/work/'||lower(owner)
      ||'.'||lower(table_name)||'.csv'||chr(39)
      ||' replace into table '||owner||'.'||table_name
      ||' fields terminated by '||chr(34)||'^'||chr(34)
      || ' optionally enclosed by '
      || chr(39)||chr(34)||chr(39)||' trailing nullcols ('
  from dba_tables
  where owner like 'APPOWNER'
union
select owner s1,table_name s2,c.column_id s3,'prompt     '
      ||chr(34)||c.column_name||chr(34)||
      decode(column_id,
         (select max(column_id)
         from dba_tab_columns d
         where d.owner=c.owner
         and d.table_name=c.table_name),
         ')'  ,  ','   )
      from DBA_TAB_COLUMNS c
  where c.owner like 'APPOWNER'
union
select owner s1,table_name s2,999 s3,'spool off '  SOURCE
       from dba_tables
  where owner like 'APPOWNER'
       order by 1,2,3
;

-- Now you need to run the following, to create the files:

Set pages 0
Set lines 9999
Set trimspool on
Set heading off
spool run_me.sql
select source from system.TOOL_CR_CTL order by s1,s2,s3;
spool off
@run_me.sql

Example output:


-- Controlfile:   emp.ctl
load data infile '/tmp/work/scott.emp.csv' replace into table SCOTT.EMP fields terminated by "^" optionally enclosed by '"' trailing nullcols (
"EMPNO",
"ENAME",
"JOB",
"MGR",
"HIREDATE",
"SAL",
"COMM",
"DEPTNO")

No comments:

Post a Comment