Automating SQL Loader Jobs Urgent!!!!

[palwinder]

Hi ,
I am using oracle 8.1.7 .Now my problem is i am receiving data in the form of flat files at regular intervals (say an hour) I have to keep on updating my database with the arrived data to keep it upto date.I am running Oracle on top of SCO Unixware. So is there any way by which i can automate this loading operations. Does this require attention at oracle end or at the OS end? My flat file also contains some headers so before loading data i want to trim my flat too.
thanx in advance

 

[sem]

I think, that the best strategy is to write some server side script, calling sql*loader and schedule it using cron.
Though, if these files are sufficiently small, you may try to use utl_file called from Oracle job. You may also use Perl+DBI

 

[Turkbear]

Hi,
If you need to do this on several ( or many) tables and it is critical data you may want to investigate a good ETL tool to automate that process - the one I use ( DataStage from Ascential Software) can be programmed to wait for a file, load it into your Oracle table ( after processing it on a row by row basis to handle any trimming of non-valid rows or doing other transformations of the source data) and email you the results of the job )

I won't put their address in this posting ( I do not want to commercialize the group ) but you can look them up..
There are also other ones out there ( one from Informatica and others) so, if that is something that would help you, look into them...

 

[skunkmaster]

Could write a unix script - something like the following. This could be set to run in the cron every day first thing. It will then sit in memory checking for files every minute then exit at 12:59. This uses the korn shell. The commands will be different if you use the c shell etc.

# Script to update flat file to Oracle
# Checks every minute and updates the file using sqlloader
# Could add echo statements to output messages to a log file
leave=n
sleep_time=60

while [ $leave = "n" ]
do
# check for the file (could use -s here also)
if [ -f /appl/data/data-file ]
then
# Run your sqlloader command
sqlldr ....
# Remove the file (or copy somewhere else as a backup)
rm /appl/data/data-file
else
sleep $sleep_time
fi
# Its nearly midnight - exit the script
hour=`date +%H`
min=`date +%M`
if [ $hour = "23" ]
then
if [ $min = "59" ]
then
leave="y"
fi
fi
done

You could also use grep to remove lines you dont want e.g
this would remove the header line from the file
mv data-file tmp-file
cat tmp-file | grep -v "Forename,Surname" > data-file

 

[palwinder]

thanx guys i am about to user your ideas.