Search This Blog

Friday, March 5, 2010

SQLLDR - Shell script to load Multiple files in table

Based on request from one of our reader, here is the post to read file from a directory and then call sqlloader command to load data file into a table.
Assumptions:
1) There has to be some way/standard to recognize the file name. In my case its a .dat file starting with sample. If you don't know data file name then create a directory specific to the load and select all data files from that directory.
2) The file format has to be same so that same .ctl file can be used to read the file and load the table.
3) Once loaded the file is then archived.

Control file code


LOAD DATA
insert into table sv_temp
fields terminated by '|' optionally enclosed by '"'
(first_col
,second_col
)


Shell Script Code

cd $CUSTOM_TOP/data
for file in sample*.dat
do
sqlldr userid=$login control=$CUSTOM_TOP/bin/sv_test.ctl data=$CUSTOM_TOP/data/$file
mv $CUSTOM_TOP/data/$file $CUSTOM_TOP/archive/$file
done


3 files sample1.dat, sample2.dat, sample3.dat was copied in CUSTOM_TOP/data directory.
Sample Output is

SQL*Loader: Release 8.0.6.3.0 - Production on Fri Mar 5 13:24:18 2010

(c) Copyright 1999 Oracle Corporation. All rights reserved.

Commit point reached - logical record count 8
Commit point reached - logical record count 9

SQL*Loader: Release 8.0.6.3.0 - Production on Fri Mar 5 13:24:18 2010

(c) Copyright 1999 Oracle Corporation. All rights reserved.

Commit point reached - logical record count 4
Commit point reached - logical record count 5

SQL*Loader: Release 8.0.6.3.0 - Production on Fri Mar 5 13:24:18 2010

(c) Copyright 1999 Oracle Corporation. All rights reserved.

Commit point reached - logical record count 7

9 Comments:

nicetoknow said...

Thank you.
Will try it out.
thanks for your time

Anonymous said...

this works great. thank you.
you saved my time :-)

Muthuramalingam said...

Hi
Excellent program. It really great effort.

Thanks for this posting.

Thanks and Regards
Muthu N

sandy.ph said...

Nice info :)
Thanks a lot

Sandy PH

Unknown said...

Could you please explain how to insert multiple files into multiple tables in script.

Regard,
Asit

Unknown said...

Hi suresh ,

Could u plz help me on this requirement.

i m loading data to staging tables from flat files for that i create a concurrent program and i will be passing parameters for that. that parameter should also load into my staging table.
reply me here plz ravisontineni@gmail.com

thanks,
ravindar.

Anonymous said...

Hi,

I have requirement to load billions of record into 5 different tables , each one of these tables have different data files. These 5 tables will be populated and truncated next day to load fresh data.

Que1 : How do I load data into 5 different table using 1 control fle?
Que2: Do I need 5 different discard, log and bad files to keep track of these 5 different loads?
Que3 : What is better and efficient way to load billions of records daily - using 5 different control table , 5 discard ,5 log file OR sing just 1 control table will solve the purpose.
Que4: What one of 5 load fails then I need to rerun the sqloader for all 5 tables again?

Note : As of now we are loading data into one table but it is taking 5-6 hours to load, so we are looking for better performance.
Thanks
Sandy

Anonymous said...

Use external table to load these files. Hope this helps.

Anonymous said...

Very Helpful :)

Copyright (c) All rights reserved. Presented by Suresh Vaishya