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")