Home » RDBMS Server » Server Utilities » Automate SQL LOADER in UNIX environment
Automate SQL LOADER in UNIX environment [message #194061] Wed, 20 September 2006 11:12 Go to next message
swisstar
Messages: 2
Registered: September 2006
Location: Canada
Junior Member
Hi,

We are using Oracle 9i in Unix environment. We want to automate sql loader through UNIX scripting. Here is the scenario:

Data files in csv format come on the network drive on daily basis with a different file name everyday for example:
test_sep20_2006.csv
test_sep21_2006.csv ...

So soulution would be, to first check the existence of the data file for the current day and then automatically picks the data file (data file names are different everyday therefore we can not hardcode the data file in the control file).

Any help would be much appreciated.

Regards
Re: Automate SQL LOADER in UNIX environment [message #194114 is a reply to message #194061] Wed, 20 September 2006 17:10 Go to previous messageGo to next message
kimant
Messages: 201
Registered: August 2006
Location: Denmark
Senior Member
If You really want to do this in Unix, this is not really a question about Oracle technology.
You would perhaps create a parameter file with the columns etc., and then the shell variables for the filename.
This is really a question to a Unix/shell developer.

Another approach would be to use external tables, where You use utl_file or even Java to test if the file are there, then define the external table, load from it and then perhaps drop it again.

Br
Kim
Re: Automate SQL LOADER in UNIX environment [message #194360 is a reply to message #194061] Thu, 21 September 2006 12:01 Go to previous messageGo to next message
swisstar
Messages: 2
Registered: September 2006
Location: Canada
Junior Member
Kim,

Thanks for the feedback. I can not use the external table approach because data files come on the network drive and for external table, file must be accessible on the database server.
Re: Automate SQL LOADER in UNIX environment [message #194369 is a reply to message #194061] Thu, 21 September 2006 13:54 Go to previous messageGo to next message
Anjumc
Messages: 2
Registered: September 2006
Junior Member
How about this..

This is just an example you can modify this..


ls ~/*.csv|grep `date|awk '{print $2"_"$3"_"$6}'` 1>/dev/null 2>/dev/null
if [ "$?" = "0" ]
then
ls ~/*.csv|grep `date|awk '{print $2"_"$3"_"$6}'`
echo "File exists"
else
echo "No file"
fi
Re: Automate SQL LOADER in UNIX environment [message #194371 is a reply to message #194061] Thu, 21 September 2006 14:22 Go to previous message
Anjumc
Messages: 2
Registered: September 2006
Junior Member
or you can use

ls ~/*.csv|grep `date +'%b_%d_%Y'``
if [ "$?" = "0" ]
then
ls ~/*.csv|grep `date +'%b_%d_%Y'``
echo "File exists"
else
echo "No file"
fi
Previous Topic: Oracle 9i to Oracle 9i without using imp or exp utility
Next Topic: data migration from excel file to oracle 9i
Goto Forum:
  


Current Time: Sun Dec 04 08:53:27 CST 2016

Total time taken to generate the page: 0.09942 seconds