Shell Programming and Scripting

hi,

I have a script that will logon to a database siebel and do the select query and then get the result in command prompt of unix.Below the script.

#!/bin/ksh


. $HOME/conf/systemProperties/EnvSetup.properties

#set -x
while read i
do
echo $i

connect1=`sqlplus -silent siebel/home1@ednamip.world <<END
set pagesize 0 feedback off verify off heading off echo off
SELECT Premise.X_CLIENT_PRMSE_ID,
meter.X_UDC_ASSET_ID
FROM
SIEBEL.S_ASSET Meter,
SIEBEL.S_ASSET SDP,
SIEBEL.s_asset_rel ARSM,
SIEBEL.S_ADDR_PER Premise,
SIEBEL.S_ASSET Route,
SIEBEL.s_asset_rel ARSR
WHERE
-- sdp to meter
ARSM.par_asset_id = SDP.row_id AND
ARSM.asset_id = Meter.row_id AND
ARSM.relation_type_cd = 'SDP-METER' AND
ARSM.x_rel_status = 'Active' AND
-- SDP to PREMISE
SDP.PER_ADDR_ID = Premise.row_id AND
-- SDP to Route
ARSR.par_asset_id = Route.row_id AND
ARSR.asset_id = SDP.row_id AND
ARSR.relation_type_cd = 'ROUTE-SDP' AND
ARSR.x_rel_status = 'Active' AND
Premise.X_CLIENT_PRMSE_ID = '$i'
exit;
END`
echo "$connect1" >> /ednadtu3/u01/pipe/naveed/class/nav1.txt
echo "Completed for $i"
done < TNS_AMRI_INACTIVE.txt
--------------------------------------------------------------------------contents of TNS_AMRI_INACTIVE.txt

0001636564
0001644841
0008076686
0001644965
--------------------------------------------------------------------------

result should i get in a file nav1.txt is:

0001644965 092276477LG
0008076686 090030615LG
0001644841 090845090LG
0001636564 090845086LG
-----------------------------------------------------------------------

but the file nav1.txt showing the below error when i cat it:

exit
*
ERROR at line 21:
ORA-00933: SQL command not properly ended
exit
*
ERROR at line 21:
ORA-00933: SQL command not properly ended
exit
*
ERROR at line 21:
ORA-00933: SQL command not properly ended
exit
*
ERROR at line 21:
ORA-00933: SQL command not properly ended

--------------------------------------------------------------------------

but the query is working fine in databse. plz help me in this?

Replace:

Code:

connect1=`sqlplus -silent siebel/home1@ednamip.world <<END

with

Code:

cat <<END

and remove the ending "backtick" after the closing END. Then check your output file to see if you find the sql commands that you expected. If so, copy and paste one command into the interactive sqlplus, and report if it works.