| creating a cron job fails [message #181046] |
Thu, 06 July 2006 08:34  |
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 #181049 is a reply to message #181046] |
Thu, 06 July 2006 08:39  |
 |
Mahesh Rajendran
Messages: 10708
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 |
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
