Passing parameters to control file

[anijan]

Hi,
I have to load some ten csv files to 10 different tables in a particular schema say TEST. Is there any way to automate it, instead of writing 10 different control files for each pair of CSV file and table.?
I am using sqlldr to load the tables.

sqlldr $usrpass $ctlfile log=$logfile bad=$badfile data=$datfile discard=$dscfile

Kindly help me in this regard.

[PAVB]

all depends on what you mean by "automate" and how complex control specs are.

[anijan]

I have some 25 input CSV files which has over 30 coulumns in each input CSV file.
for example if i start writing control file like this ..

LOAD DATA
APPEND
INTO TABLE emp
(empno POSITION(01:04) INTEGER EXTERNAL,
ename POSITION(06:15) CHAR,
job POSITION(17:25) CHAR,
mgr POSITION(27:30) INTEGER EXTERNAL,
sal POSITION(32:39) DECIMAL EXTERNAL,
comm POSITION(41:48) DECIMAL EXTERNAL,
deptno POSITION(50:51) INTEGER EXTERNAL

and so on for 30 coulmns then the effort would be too much to create 25 control files to load 25 tables.
I feel it would be better if i have one control file so that i can pass the table name as parameter and call the same control file in a loop.

PS: each table has unique column names.

[PAVB]

Got it.

You can always write a storedproc designed to write a control file on the fly; after all Oracle system views have all the info you need - providing you are loading all columns, in the same order, etc.

It would works like this...
- Main script calls storedproc passing table-name,
- Storedproc writes control file and exits
- Main script calls sqlloader pointing to control file.

It wouldn't be a piece of cake but certainly a nice project to tackle; on the other hand I foresee a myriad of issues with input field lenghts and data types. Probably it would be cheaper in terms of effort to create the control files manually.

[LKBrwn_DBA]

If this load is NOT on-time load and you will need to repeat it periodically, then you may perhaps consider external tables.

[anijan]

Thanks a lot ur your quick reply.
Since im new to PL/SQL im not aware of storedproc.
If you could provide me with somelinks or materials it will be very useful for me..

[PAVB]

Since im new to PL/SQL

This settles it - better to write your 25 control files.

Just bing pl/sql ... plenty of documentation on the net