Hi,
I am trying to execte the .sql file(which contain the two different sql query) from solaris script using sqlplus command, the script is executing successfully but the issue is when i execute the script, it is not terminating automatically, for example if i put one sql statement then i have to press <<enter>> key once and if two sql statements then i have to press <<enter>> two times and so on. So please help me anybody on this issue because this script will be executed from the crontab, thats why i have to solve this issue.
Following is my solaris script generateTicket.sh :
sqlplus -s haweb/haweb@haweb @/apps/oracle/test/generateTicket.sql $argdate >> output.txt
if test $? -ne 0 ; then
echo 'sqlplus error'
exit 1
else
echo 'sqlplus OK - Retrieved the 9C customer information from the database...'
fi
egrep 'HEAD|TRAN' output.txt > WHA9C-$argdate.txt
rm t.txt
rm output.txt
exit 0
SQL script (.sql file which contain 2 diffent query):
SELECT 'HEAD' as head,
SEQ_TICKET_NUMBER.nextval as sequencenum,
to_char((select count(*) from transaction where TO_CHAR(END_TIME,'DD-MM-YYYY')= TO_CHAR('&1'))) as total_transaction,
lpad(to_char((select sum(PRICE_BRUT) from transaction where TO_CHAR(END_TIME,'DD-MM-YYYY')= TO_CHAR('&1'))),11) as sum_price_brut,
lpad(to_char((select sum(TVA) from transaction where TO_CHAR(END_TIME,'DD-MM-YYYY')= TO_CHAR('&1'))),11) as sum_tva,
lpad(to_char((select sum(PRICE_TTC) from transaction where TO_CHAR(END_TIME,'DD-MM-YYYY')= TO_CHAR('&1'))),11) as sum_price_tva from dual;
/
select 'TRAN' as tran,
lpad(C.SIEBELID,24) as siebelid,
rpad(C.FACTURATION,8) as facturation,
lpad(T.TRANSACTIONID,24) as transactionid,
TO_CHAR(T.START_TIME,'YYYYMMDDHHMMSS') as start_time ,
lpad(P.PARTNERID,24) as partnerid,
rpad(P.PARTNER_NAME,48) as partername,
lpad(nvl(T.ASSETID,T.SERVICEID),24) as productid,
rpad(T.SERVICETYPE,40) as servicetype,
lpad(to_char(T.PRICE_BRUT),11) as price_brut,
lpad(to_char(T.TVA),11) as tva,
lpad(to_char(T.PRICE_TTC),11) as price_ttc,
lpad('Acte',4) as acte,
lpad(C.PHONE_NUMBER,10) as phone_number,
lpad(C.SEGMENT,3) as segment
from CLIENT C, PARTNER P, TRANSACTION T
WHERE C.USER_ID=T.USER_ID AND P.PARTNERID=T.PARTNERID AND
T.STATUS=1 AND transaction_type='9C' AND TO_CHAR(T.END_TIME,'DD-MM-YYYY')= TO_CHAR('&1');
/
quit;
It will be highly appreciated if some one help me on this issue.
Thanks & Regards,
Jyotisree