Cronjob Failing

Hi all,
This is not a duplucate post, searching for an another way to get the solution.
I got a procedure call_weekly with no input parameters to be passed. It has to be xecuted on every sunday at 3:00 am. I tried very much to do it through DBMS_JOB , but couldn't . Can any one help me writing it in cron?? it's on Linux 7.2. If any body want to through some light on the same DBMS_JOB, please look into my earlier posting with subject DBMS_JOB FAILING ......

When i run it manually, its executing perfectly fine like,

SQL> EXEC CALL_WEEKLY;

Thanks in advance.

1.Create a script for connect and execute the procedure
2. Man contrab to set the new task
--> crontab -l (list the contents in cron)
--> crontab -e (edit the cron) --> Read and write

Cheers

Angel

I’m not sure about Linux. But on Unix you have to set the environment variable…


ORACLE_SID=xxxxxx
ORACLE_HOME=/u01/home/oracle/product/8.0.5
export ORACLE_SID ORACLE_HOME


Hope that help!


[Edited by dba_dada on 07-10-2002 at 08:38 AM]

Quote:

---

Originally posted by dba_dada
I’m sure about Linux. But on Unix you have to set the environment variable…


ORACLE_SID=xxxxxx
ORACLE_HOME=/u01/home/oracle/product/8.0.5
export ORACLE_SID ORACLE_HOME


Hope that help!

---

Of course or have them in your .profile ;)

Cheers

Angel

My 2 cents:

"export EDITOR=vi" so you can use crontab -e

Just because a script works while logged into a O/S user, THAT DOES NOT MEAN IT WILL WORK IN A CRON (sorry for shouting) :)

You need to path everything unless you explicitely set your environment in the script.

ie.
sqlplus scott/tigger@mydb @mysql.sql

..could possibly fail if your env is not setup in the script..or path everything like:

$ORACLE_HOME/bin/sqlplus scott/tigger@mydb @/oracle/scripts/mysql.sql

(assuming ORACLE_HOME is set).

The moral of the story: make certain your env is set explicitly in the script when running from cron or "path" everything.

Quote:

---

Originally posted by gopi
The moral of the story: make certain your env is set explicitly in the script when running from cron or "path" everything.

---

I agree. 90% of cronjob problems are because the environment is not setup correctly. You must realize that everything that is done during the login process is not done during a cron execution. I like to create a cron job that has the entry "env" which will echo your environment variables. From there you can usually diagnose what's going on...

Do a source of your oracle OS profile first of all in your script:

source ~/.profile

The source command above is for "C" shell. If you use ksh "Korn shell " try . $HOME/.profile in the top of all your croned scripts remembering to use an if statement to bypass any tty settings like your prompt of the type of terminal you are using...
e.g.
export ORACLE_SID=SID
ORAENV_ASK='NO';. oraenv;ORAENV_ASK='' # The oraenv script provided by Oracle during install!
# this works for ksh c shell syntax is different........
tty -s
if [[ $? -eq 0 ]]
then
# default term type what ever emulator.....
case $TERM in
70092|70094|hp*) export ORACLE_TERM=hp;;
dtt*) export ORACLE_TERM=vt220;tset;;
vt4*|VT4*) export ORACLE_TERM=vt220;export TERM=vt220;stty erase ^?;tset;;
vt3*|VT3*) export ORACLE_TERM=vt220;export TERM=vt220;stty erase ^?;tset;;
vt2*|VT2*) export ORACLE_TERM=vt220;export TERM=vt220;stty erase ^?;tset;;
vt1*|VT1*) export ORACLE_TERM=vt220;export TERM=vt220;stty erase ^?;tset;;
*) export ORACLE_TERM=$TERM;;
esac
export PS1="`hostname | cut -c 1-8` `whoami | cut -c 1-8` > "
fi