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
Shell Script Code
3 files sample1.dat, sample2.dat, sample3.dat was copied in CUSTOM_TOP/data directory.
Sample Output is
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
No comments:
Post a Comment