Shell Programming and Scripting

sqlplus -s $USER_ID@$SID/$PWD<<EOF>$CUR_DIR/sql_output.txt 
set feedback off
set heading off
select distinct account_no from adj  WHERE ADJ_TRANS_CODE=-2401  and request_status=1  and bill_ref_no=0
order by account_no;
EOF

in place of select statement you can write the PROCEDURE

sqlplus -s $USER_ID@$SID/$PWD<<EOF> ##here you connect to the DB EOF is marker.you can use anything

EOF

LIKE THIS

sqlplus -s $USER_ID@$SID/$PWD<<EOF>
your oracle stuff
EOF

#!/bin/sh
#######################################################################
# ORACLE ENVIRONMENT VARIABLE DECLARATION(check your ORACLE_HOME variable)
#######################################################################
ORACLE_HOME=/oracle/product/10g; export ORACLE_HOME 
PATH=$PATH:$ORACLE_HOME/bin:/bin:/usr/bin; export PATH

### Connecting to ORACLE DATABASE

echo "SQLPLUS CONNECTION"

sqlplus -s USERNAME/DB_PASSWD@ORACLE_SID<<EOF>
set feedback off
set heading off
select * from dual  ;
##write your procedures ,functions,etc here
EOF

###Check if connection to ORACLE DB was successfull of not
if [ $? -eq 0 ]
then
echo " SQLPLUS Connection Successful "
else
echo " SQLPLUS Connection Failed "
fi