Issue with crontab

I have a ksh script which will connect to a database and executes some sql scripts. If i run the ksh script it is working fine. But if i schedule it to run at a perticular time using cron the sql script is not running. The scriptl initially creates a spool file for sql script and then connects and executes the sql script. I can see the spool file created but nothing inside it.
Any idea what could be wrong when the script is run using cron?
is there any simple way to create a log file for this script ?

I am using UX-hp

Have you set the required environment variables $ORACLE_HOME, $ORACLE_SID, $PATH ...?

Use full pathnames when calling the scripts.

Yes. $ORACLE_HOME and $ORACLE_SID are set correctly. and the script uses full path name when calling other scripts. I can see the successful execution when I run the script directly from shell.

Did you look mails for this script?
is there any error or warning?

Cron runs a basic shell without setting the path, aliases, environment variables, etc. that you have when you run the script manually.

I just checked the mails and found this messge -
/home/supp1/om/abc/script2.ksh[23]: sqlplus: not found

This is how I am trying to connect to sqlplus -
sqlplus support/support@supp1 << EOF

Where support is the user name and password and supp1 is the DB.

is there anything wrong here which cron doesn't recognise?

Set the required environment variables in your script like:

#!/bin/ksh

ORACLE_SID="your_sid"
# Other oracle stuff like $ORACLE_HOME, $PATH etc 
MYVAR="www.unix.forum"
..

sqlplus support/support@supp1 << EOF
select..
EOF

yes, there is and it's been suggested earlier in the thread.
I'd suggest looking into the FAQ and searching the forum prior to any postings.

you can try full path sqlplus and try again

After setting and exporting $ORACLE_HOME we need to be able to find sqlpus in the program search PATH.

PATH=${PATH}:${ORACLE_HOME}/bin  ; export PATH

I set the required environment varibales like ORACLE_HOME and ORACLE_SID. Still not able to get in... i can see a new error now after setting the env var !

Error 6 initializing SQL*Plus
Message file sp1<lang>.msb not found
SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory

This is how I have set the parameters -

Code:

ORACLE_HOME="/opt/oracle/orcts/product/9.2.0"
ORACLE_SID="SUPP1"
SQLPLUS="${ORACLE_HOME}/bin/sqlplus -s"
.
.
.
${SQLPLUS} -s support/support@supp1 <<!
.
.
.
!

Don't forget to export the environment variables. We are setting them for the benefit of the processes we call (e.g. "sqlplus").
Also it is much better to find "sqlplus" through unix PATH. The same environment then works for all Oracle unix programs.

ORACLE_HOME="/opt/oracle/orcts/product/9.2.0"  ;  export ORACLE_HOME
ORACLE_SID="SUPP1"  ;  export ORACLE_SID
PATH=${PATH}:${ORACLE_HOME}/bin  ; export PATH


.
.
.
sqlplus -s support/support@supp1 <<!

BINGO ! its working after export.

Thanks all for the help.