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