koolbinu
asked on
cron job and SQL loader
I want to create a cron job to fetch a file(flat file) from a particular folder and run the interface to populate custom table in 10g database
Sorry for my lack of knowledge in cron job
what all parameters i should require to fetch the file from a particular directory and make the sql loader run if you can suggest me some small examples or something Iw ill appreciate it
Sorry for my lack of knowledge in cron job
what all parameters i should require to fetch the file from a particular directory and make the sql loader run if you can suggest me some small examples or something Iw ill appreciate it
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
We've been using it in 9 for a very long time. Not sure if it was available in 7 or 8, but definitely in 9.
I have an old 8 database, and it is available there as well.
OK, thank you. I thought that had been a requested enhancement for SQL*Loader for years that didn't get implemented. (This is just another reminder for me that even though this site may consider me a "genius", there are still things in Oracle that I don't know - but I knew that anyway.)
ASKER
I had a question I do have the control file ready do I just have to type the cron tab command at unix editor and everything will be set
You need to have a script around the SQL*Load command. It is possible to put it into a very long command line in cron, but not worth it.
Depending on what variant of UNIX you are using the command is usually:
crontab -e
This will open a copy of the cron file and allow you to edit it.
Depending on what variant of UNIX you are using the command is usually:
crontab -e
This will open a copy of the cron file and allow you to edit it.
After getting the controlfile ready for SQL*Loader, I would say the next step is to write a shell script that calls SQL*Loader and passes it the name of the data file and the name of the controlfile, then test that to make sure it works when you run it interactively. Then adjust the shell script to set the environment variables so it will also work when called from cron. Finally, you edit the cron file to add a job that runs this shell script at the time you want.
Are you familiar with the syntax for cron files and the commands to set the environment variables? If not, which version of UNIX or Linux do you have on your server?
Are you familiar with the syntax for cron files and the commands to set the environment variables? If not, which version of UNIX or Linux do you have on your server?
ASKER
can somone help me with the script to call the SQL loader
The script to call SQL Load is only part of it. What else does the script need to do?
The shell of a script to call SQL Load is:
#!/bin/sh
ORACLE_HOME=<path>
ORACLE_SID=<sid>
PATH=${PATH}:$ORACLE_HOME/
export ORACLE_HOME ORACLE_SID PATH
sqlldr control=<full_path>/<ctl_f
The shell of a script to call SQL Load is:
#!/bin/sh
ORACLE_HOME=<path>
ORACLE_SID=<sid>
PATH=${PATH}:$ORACLE_HOME/
export ORACLE_HOME ORACLE_SID PATH
sqlldr control=<full_path>/<ctl_f
Forced accept.
Computer101
EE Admin
Computer101
EE Admin
Can the name of the data file for SQL*Loader really be specified as a parameter on the command line? Is that something I have been missing for years, or is that new in Oracle10 or Oracle11?