SQLPLUS Command using cronjob

Spend three days to figure out how to copy an oracle DB table from a cronjob.

After some intensive surfing for reference online, finally realize what I miss is the declaration.
It is worthy to note down:

For sh:

#!/bin/sh

ORACLE_HOME=/DB/HOME
export ORACLE_HOME
ORACLE_BASE=/DB/HOME
export ORACLE_BASE
ORACLE_OWNER=oracle
export ORACLE_OWNER
ORACLE_SID=MYDBID
export ORACLE_SID
NLS_LANG="american_america.we8iso8859p1"
export NLS_LANG

HOME=/HOME/DIR/

PATH=/usr/bin:/bin:/usr/local/bin:$ORACLE_HOME/bin
export PATH

sqlplus -s username/password@MYDBID @${HOME}/script.sql >> ${HOME}/something.txt

For csh:

#!/bin/csh

setenv ORACLE_HOME /DB/HOME
setenv ORACLE_BASE /DB/HOME
setenv ORACLE_OWNER oracle
setenv ORACLE_SID MYDBID
setenv NLS_LANG "american_america.we8iso8859p1"

set HOME = /HOME/DIR/

setenv PATH /usr/bin:/bin:/usr/local/bin:$ORACLE_HOME/bin

sqlplus -s username/password@MYDBID @${HOME}/script.sql >> ${HOME}/something.txt

-hayashi

Script that Generates Report from Oracle DB

------------------------------Script---------------------------------

#!/bin/sh

ORACLE_HOME=/opt/oracle
export ORACLE_HOME
ORACLE_BASE=/opt/oracle
export ORACLE_BASE
ORACLE_OWNER=oracle
export ORACLE_OWNER
ORACLE_SID=DB_NAME
export ORACLE_SID
NLS_LANG="american_america.we8iso8859p1"
export NLS_LANG

PATH=/usr/bin:/bin:/usr/local/bin:$ORACLE_HOME/bin
export PATH

outfile="/export/home/`date +"%Y%m%d"`.txt"

sqlplus -s username/password@DB_Name @$HOME/gen_report.sql > $outfile

----------------------------SQL File--------------------------------

select USER_ID as A, B, to_char(TIMESTAMP,'hh:mi:ssam') as Time From Table WHERE to_char(TIMESTAMP,'DD-Mon-YY') = (select to_char(sysdate, 'DD-Mon-YY') from dual) AND CONDITION_A = 'true' order by timestamp;

whenever sqlerror exit 99
whenever oserror exit 88
set pages 1000 echo off verify off feedback off

alter session set nls_date_format='YYMMDD HH24:MI:SS';
set line 200
col user_id format a15
col remarks format a50

select USER_ID as A, B, to_char(TIMESTAMP,'DD-Mon-YY hh:mi:ssam') as Time From username.table_name WHERE to_char(TIMESTAMP,'DD-Mon-YY') = (select to_char(sysdate-1, 'DD-Mon-YY') from dual) AND Condition_A = 'true' order by timestamp;

exit

Spool from an Oracle DB as .CSV file

set echo off
set feedback off
set pagesize 0
Spool backup.csv;
Select cellno||','||calldate||','||answertime from temp;
Spool off;