SQL Loading files

[drkestrel]

I want to use SQL Loader to load all files of a known extension from a specific directory into a single table.
I have referred to the Oracle 8i documentation, but whereas I could have multiple INFILE clause, I can't find something like INFILE *.DAT, etc. etc...

Is that possible?

 

[MikeJones]

I Don't think so, I think you wou;ld have to write a shell script (Assuming this is on Unix that is) to load each file with the same control file (You don't have to specify the infile in the control file), each file would load with the append option so you would probably need to run a script to truncate the table first.

HTH,

Mike.

 

[DBAwhosaysNIE]

another way might be to set up and load from a pipe w/a cat *.DAT

hadn't actually tried this w/SQL*Loader (have imported from uncompress pipe however) but (in theory) it should work and would allow you to pull everything in a single run so you wouldn't end up with a bunch of fragmented extents if using direct path.

 

[drkestrel]

cating??
do you mean using some kind of shell scripts to cat the data files before using SQL Loader to load the files?

I notice when loading files using SQL Loader on Oracle 8i that (by default?), inserts are commited in batches of 64rows a time.

I am just wondering, any chance of setting this to someting like 500? (if so what would happen if I only have 300 rows to insert..) I assume the 2PC mechanism would mean that should anything crash mid way through a loading, any uncommited writes would be discarded? However, what are the chances of this happening on a UNIX box.. ??

 

[MikeLacey]

dkestrel,

The SQLLDR program reads from a single file -- or -- it reads from its stdin, so, as long as the data files are all in the same format, you can feed it data to load like this:

cat *.dat | sqlldr <other parameters> Mike
michael.j.lacey@ntlworld.com
Email welcome if you're in a hurry or something -- but post in tek-tips as well please, and I will post my reply here as well.