Home » Infrastructure » Linux » creating a cron job fails
creating a cron job fails [message #181046] Thu, 06 July 2006 08:34 Go to next message
alanm
Messages: 284
Registered: March 2005
Senior Member

Hello All,
hope you can help with this one.

I have created a script file

#!/usr/bin/bash
ORACLE_SID=care; export ORACLE_SID
ORACLE_HOME=/u00/app/oracle/product/9i

#banner hello world
sqlplus -s '/ as sysdba'<<EOF
prompt "daily report"
#prompt "copy and paste data"
@spc.sql
exit;
EOF

this works fine from the command line as folows
/home/oracle/report.ksh > /home/oracle/report.log.`date`

it does not however work when run from the crontab.

regards

Alan.
Re: creating a cron job fails [message #181048 is a reply to message #181046] Thu, 06 July 2006 08:39 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>it does not however work when run from the crontab.
My car does not work when it is cold, please tell me how to fix it.
More than likely sqlplus is not found within PATH for cron process.
Re: creating a cron job fails [message #181049 is a reply to message #181046] Thu, 06 July 2006 08:39 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
First, using a sysdba account for just reporting is a bad idea.
>>@spc.sql
cron will not understand the location of the called sql file.
So within the script, provide a complete path to spc.sql.
And in cron, redirect the output to /tmp or elsewhere.
something like this.
15,30 5 * * 1,5 /home/oracle/scr/app/reminder >/tmp/reminder 2>&1


Edit:
By the way, why are you calling the sql file inside a 'here' document? Beats the purpose of here document.
simply call the spc.sql in sqlplus command line (include all formatting inside spc.sql)

sqlplus -s  user/pass@service @/mypath/../spc.sql
or
sqlplus -s user/pass@service <<EOF
..
all conetnts of spc.sql
..
..
exit;
EOF

[Updated on: Thu, 06 July 2006 08:43]

Report message to a moderator

Re: creating a cron job fails [message #181058 is a reply to message #181046] Thu, 06 July 2006 09:41 Go to previous message
alanm
Messages: 284
Registered: March 2005
Senior Member

thanks for the replies. Ok I have changed the script

ORACLE_SID=care; export ORACLE_SID
ORACLE_HOME=/u00/app/oracle/product/9i; export ORACLE_HOME
PATH=$ORACLE_HOME/bin; export PATH

#banner hello world
$ORACLE_HOME/bin/sqlplus -s '/ as sysdba' @/home/oracle/spc.sql
exit;

low and behold it works.
thanks guys

regards

Alan.
Previous Topic: How to start isqlplus on RH Linux9.0
Next Topic: date field in file name
Goto Forum:
  


Current Time: Thu Mar 28 04:54:09 CDT 2024