How to pass Variable from shell script to select query for SqlPlus?

echo "set echo off"; echo "set feedback off"; echo "set linesize 4000"; echo " set pagesize 0"; echo " set sqlprompt ''"; echo " set trimspool on"; 

Select statement is mentioned below
echo "select res.ti_book_no from disney_ticket_history res where res.ti_status =${STATUS} and res.request_file_id IN (select req.request_file_id from disney_ticket_request_file req  where transaction_date=${TRANSACTION_DATE});") | ${ORACLE_HOME}/bin/sqlplus -S tipls/tipls@plsdev)

OUTPUT IN SQL WITH ERROR:
select res.ti_book_no from disney_ticket_history res where res.ti_status =('\''PENDING'\'') and res.request_file_id IN (select req.request_file_id from disney_ticket_request_file req  where transaction_date=09212013)

ERROR at line 1:
ORA-00911: invalid character

Error is due to highligted red colour. I need to pass it as a string like 'PENDING' through script

The simplest is to use a 'here-document':

sqlplus /nolog << EOF
connect user/pass
set pagesize 0 feedback off ...
...
select ...
...
EOF

Scott Can you please be more specific. Do I have to use another script like sqlscript. I am just a begginer so asking. Dont have much idea.

A 'here-document' is a construct you can find information about from the man-page for your shell.

Taking what you posted, something like:

sqlplus -S /nolog << EOF
connect tipls/tipls@plsdev
set echo off feedback off linesize 4000 pagesize 0 sqlprompt '' trimspool on

select res.ti_book_no
  from disney_ticket_history res
 where res.ti_status = '$STATUS' and res.request_file_id
    in (select req.request_file_id
          from disney_ticket_request_file req
         where transaction_date='$TRANSACTION_DATE');
EOF

Thanks Scott it works now. let me know how to write the output of this statement in a file as well

sqlplus -S /nolog << EOF > file.out
...
EOF
It worked fine. but how to put the sql output into the xls file.

Now How do i send it to excel sheet.
I have done some part but dont know how to append the sql output

FILENAME: DISNEY09212013.xls
Header Row:
<html><body><table border='1'><caption>DISNEY BOOKING<br/>Delivery Date: ${TDATE_SLASH}</caption><tr><th colspan="13" bgcolor="black"></th></tr><tr bgcolor='#D3D3D3'><th>BOOKING</th><th>ARRIVAL DATE</th><th>STATUS</th><th>LAST_NAME</th><th>FIRST_NAME</th></tr>" >${DISNEY_REPORT_HOME}/${DISNEY_FILE}
+ echo '<html><body><table border='\''1'\''><caption>DISNEY BOOKING<br/>Delivery Date: 09/21/2013</caption><tr><th colspan=13 bgcolor=black></th></tr><tr bgcolor='\''#D3D3D3'\''><th>BOOKING</th><th>ARRIVAL DATE</th><th>STATUS</th><th>LAST_NAME</th><th>FIRST_NAME</th></tr>

Then The SQL QUERY

sqlplus -S /nolog << EOF  >> DISNEY09212013.xls
connect tipls/tipls@plsdev
set echo off feedback off linesize 4000 pagesize 0 sqlprompt '' trimspool on

select res.ti_book_no ,res.arrival_date ,res.ti_status,res.last_name,res.first_name from disney_ticket_history res where res.ti_status ='PENDING' and res.request_file_id IN (select req.request_file_id from disney_ticket_request_file req  where transaction_date=${TRANSACTION_DATE});

EOF


</table></body></html>" >>DISNEY09212013.xls

Desired Output on file DISNEY09212013.xls(Its not compulsory xls file it can be any file where the output can be easily written:
########################
DISNEY BOOKING												
Delivery Date: 09/21/2013												
												
BOOKING	ARRIVAL DATE	STATUS	LAST_NAME	 FIRST_NAME								
X               09222013           PENDING    ABC            XYZ
A               10122013           PENDING    KLM            EFG
#######################

---------- Post updated 09-26-13 at 01:56 PM ---------- Previous update was 09-25-13 at 11:25 PM ----------

Any Help will be appreciated.Thanks,

You can't write .xls files. What you can do is write .csv files that can easily be imported into EXCEL. Try spooling into a file and use sth like colsep=";".