cron and sqlldr

[tron]

anyone know how to run sqlldr by using a crontab?

i can run sqlldr fine via a perl script or shell script, but when i call the script(s) from a crontab then i get an error telling me that it can find sqlldr. if i type the whole path in the script then i get a different error. any ideas?

[sreddy]

Crontab tbl_load.sh script at whatever schedule you wanted.

<font face=courier>
::::::::::::::
tbl_ldgrload.sh
::::::::::::::

#!/bin/csh -f
#
echo "Starting ctlipt $0 on `date`."
setenv ORACLE_SID xyzz
setenv ORAENV_ASK NO
source /usr/bin/coraenv
#
echo sqlldr control=l_budl parfile=ldirp.par
sqlldr control='/data2/xyzz/ctl/l_budl' log='/data2/xyzz/log/l_budl.log' parfile
=ldirp.par
#
echo sqlldr control=l_clprj parfile=lconv.par
sqlldr control='/data2/xyzz/ctl/l_clprj' log='/data2/xyzz/log/l_clsprj.log' parfile=ldirp.par
#
echo sqlldr control=l_fdled parfile=ldirp.par
sqlldr control='/data2/xyzz/ctl/l_fdled' log='/data2/xyzz/log/l_fdled.log' parfile=ldirp.par
#
echo "Script $0 on `date` has finished."
#End of file.

::::::::::::::
lcnvp.par
::::::::::::::
userid = xyzz/*****
rows = 2000
discardmax = 10
errors = 100
direct = false
silent = (discards)

::::::::::::::
ldirp.par
::::::::::::::
userid = xyzz/*****
rows = 2000
discardmax = 10
errors = 100
direct = true
silent = (discards)
</font>

[tron]

err...forgive me for the ignorance...i am a little new to oracle. i basically know how to use sqlldr like so

prompt$ sqlldr username/passwd control=somefile.dat

so in my scripts this was as about as complicated as i got. this worked fine with the scripts, but when i ran the cron command i got errors. i could not find /usr/bin/coraenv on the box i have this script on. what does it mean?

thank you for your help...it is appriciated.

[tron]

the above should be somefile.ctl not .dat

[Dave_A]

Hi Tron,

I think that the problem you are encountering is because jobs submitted through cron do not get the same environment set up for them as when you log in to the server and run them interactively. There are a couple of simple solutions:

1) Run the script from a privileged user and schedule an su command instead of scheduling the script directly. e.g. run the script from the root crontab, as follows:

[schedule_info] su - [oracle_user] -c [name of script] >[log_file] 2>>&1

obviously, in the above, objects enclosed in square braces require your system-specific information inserting in their place.

2) At the start of your script, set up the environment for the script (ORACLE_SID, ORACLE_HOME, any environment variables required in the script - this is what the previously posted script was doing. You *may* be able to achieve this by 'sourcing' your .profile in the first line of the script. ('sourcing' a file is running it within the current shell environment, and is achieved by preceeding the script name with '. ' (dot space)), as below

. /[path_to_login_directory]/.profile


HTH

David.