SQL Loader...

[nithin97]

hi, i need to load a table using sqlloader every 15 minutes.. and the thing is the infile's name will be changing every 15 minutes but with a definite format like xyz_20020709_1015.txt,xyz_20020709_1030.txt etc..
can you suggest me a way for loading the table ? is it possible to invoke sqlloader using a unix shell script or anyother way to do this ? your help appreciated....

 

[lmorgansky]

If you are running on UNIX...

The easiest way is to replace the file name in your control file with a made-up character bracketed variable. For example:

^inputfile^

Then use a tool like "sed" in a script to create a new control file from the old one, replacing instances of your variable above with the new file name. The example below assumes you have built your new file name and stored it in a shell variable:

cat ctrlfile.ctl | sed "s/^inputfile^/${newname}/g" > newctrlfile.ctl

 

[nithin97]

hi lmorgansky, thanks for your suggestion.. can i pls know how to invoke SQLLoader from a shell script, coz i'm new to both SQLLoader and Unix Shell scripting...thanks...

 

[lmorgansky]

As far as I know SQL*Loader is only executeable from the command line. Any command you execute from a command line, can be executed from a script.

If you are new to SQL*Loader, take a look at the following:

http://download-east.oracle.com/docs/cd/A87860_01/doc/server.817/a76955/ch04.htm#644

If you are new to UNIX shell scripting, I recommend:

http://www.oreilly.com/catalog/korn2/

 

[rcurva]

You can also assign a default infile filename. Logic will be;

1. Wait for the file, say every 5 mins.
2. Rename the file to a default filename once received.
mv $fileloc/*.txt $fileloc/default_file.txt
3. load via sqlldr without changing your control file. Your infile will always load default_file.txt file.

The only catch here is, the loading must be finished before the next file comes in, otherwise, you may need to put some check-and-wait procedure in your script.

How's the idea?

Robbie

"The rule is, not to besiege walled cities if it can possibly be avoided" -- Art of War

 

[nithin97]

thanks morgan and curva, i'll try that and let yu know... but now i'm not able to run sql*loader from my unix command prompt.. what would be the problem ? should i set the path ?

 

[lmorgansky]

Sounds like a PATH problem. Make sure you have ORACLE_HOME set in addition to other Oracle variables. Then make sure ORACLE_HOME/bin is in your PATH.

You really should consult the setup and installation guides. If you do not know how to set the PATH, then you are going to need a basic UNIX book too.

L