DJ Dunk

iTrader: (5)

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 ?

cottonfoo

Are the sqlldr rules the same for each cloned table?

DJ Dunk

iTrader: (5)

Yes they are.

cottonfoo

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.

DJ Dunk

iTrader: (5)

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

cottonfoo

Assuming you can pass the table name:

Let's try this edit.

DJ Dunk

iTrader: (5)

Still

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

cottonfoo

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?

DJ Dunk

iTrader: (5)

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

cottonfoo

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

DJ Dunk

iTrader: (5)

Gotcha I'll try it out, thanks.