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:
Thank you.
Will try it out.
thanks for your time
this works great. thank you.
you saved my time :-)
Hi
Excellent program. It really great effort.
Thanks for this posting.
Thanks and Regards
Muthu N
Nice info :)
Thanks a lot
Sandy PH
Could you please explain how to insert multiple files into multiple tables in script.
Regard,
Asit
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.
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
Use external table to load these files. Hope this helps.
Very Helpful :)
Post a Comment