I have a large number of data dumps that need to be loaded into tables easily using SQL*Loader.

There are 9 different table structures which are cloned about 50 times, so 450 tables to load into.

I have a CTL file for each of the 9 different structures, each tailored for their own columns. I will have 450 data files that need loading into these tables.

If I have a list of the tables and a list of the data files. How can I quickly create the required CTL files ? I need 450 CTL files with the input file and table name within it (I could put the input file in the command line, so at a minimum I need the table name in there). Or can I put the target table in the command line too ?

I intend to write a shell script to run the 450 sqlldr commands when they're all done.

Any ideas on how best to do this ?

 

Are the sqlldr rules the same for each cloned table?

 

Yes they are.

 

You just need a couple of loops. An outer loop to read in each .ctl file per table, then another loop to loop over each cloned table and run sqlldr against that.

 

Sorry, you've lost me. Can you explain further ?

 

Assuming you can pass the table name:

Let's try this edit.

 

Still

I have 450 tables and 450 different files to load, each into a specific one of the 450 tables.

 

Maybe I misunderstood. If the .ctl files are the same for each cloned table (the 50 clones of each of the 9 tables) then you only need one .ctl file for each table type, you just need to specify each cloned table to the .ctl, no?

 

Correct. How do I specify which table to load into ? I thought that had to be within the CTL file ?

 

In your shell script just set a $TABLE (or whatever) variable, and use that env var in your .ctl files.

 

Gotcha I'll try it out, thanks.