unable to run sql query from CRON

Question

I need to add my script to cronjob. but it is not getting connected to sqlprompt and executing my query. Cron works fine for other commands which are not inside sqlprompt #!/bin/bash

sql=/instance_name/../product/12102 // ORACLE_HOME loc

connnect=$($sql -s "/ as sysdba" <<EOF
spool h1.txt
select file_name from dba_data_files where tablespace_name='RTSPACE';
spool off;
exit
EOF)
cat "h1.txt" | mail -s "test" xyz@yahoo.com
exit

Problem I am facing is, script is not getting connected to sqlprompt. I am currently getting mail. but the ouput of query is not getting read. so my body of the mail is blank.

Answer 1

I Found the solution. just source ~./profile for any shell other than bash and source ~./bash_profile for bash shell. redirect both to >/dev/null incase of any error. this shud connect to your sql prompt.

source ~./bash_profile >/dev/null
source ~./profile >/dev/null

sqlplus -s "/ as sysdba" <<EOF
spool h1.txt
select file_name from dba_data_files where tablespace_name='RTSPACE';
spool off;
exit
EOF)
cat "h1.txt" | mail -s "test" xyz@yahoo.com
exit