SQLPLUS unable to execte query automatically from solaris script


 
Thread Tools Search this Thread
Operating Systems Solaris SQLPLUS unable to execte query automatically from solaris script
# 1  
Old 08-10-2008
SQLPLUS unable to execte query automatically from solaris script

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
# 2  
Old 08-11-2008
Try "set pagesize 0" at the top of your sql script, and possibly you take out the slash between the two scripts. I think you only have to end the whole script with / then quit.
# 3  
Old 08-11-2008
thanks

thanks a lot, its working
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Shell Script (ksh) - SQLPlus query filter using a string variable

Using ksh, I am using SQLPlus to execute a query with a filter using a string variable. REPO_DB=DEV1 FOLDER_NM='U_nmalencia' FOLDER_CHECK=$(sqlplus -s /nolog <<EOF CONNECT user/pswd_select@${REPO_DB} set echo off heading off feedback off select subj_name from subject where... (5 Replies)
Discussion started by: nkm0brm
5 Replies

2. Shell Programming and Scripting

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... (7 Replies)
Discussion started by: aroragaurav.84
7 Replies

3. Solaris

Error during running sqlplus command from shell script in Solaris

I am using following code to connect to oracle database from solaris shell script. which will try thrice to connect the database ...at the 4rth atempt it will exir=t. count=0 while ; do sqlplus -s $usrname/$password@dbSID <<-EOF | tee $logfile WHENEVER OSERROR EXIT 9; WHENEVER SQLERROR... (4 Replies)
Discussion started by: millan
4 Replies

4. Shell Programming and Scripting

Problems with storing oracle sqlplus query output shell script

Hello everyone, I have a RHEL 5 system and have been trying to get a batch of 3-4 scripts each in a separate variables and they are not working as expected. I tried using following syntax which I saw a lot of people on this site use and should really work, though for some reason it doesn't... (3 Replies)
Discussion started by: rockf1bull
3 Replies

5. Solaris

Unable to start SQLPLUS

I upgraded my system to Solaris 11 and everything works but I can't start my database or what (I am a noob). OS: -bash-4.1$ uname -a SunOS Solaris11 5.11 11.0 i86pc i386 i86pc -bash-4.1$ isainfo -kv 64-bit amd64 kernel modules Database version is 11g2 Enterprise edition, installed on... (6 Replies)
Discussion started by: solaris_user
6 Replies

6. Shell Programming and Scripting

SQL query in a loop with single sqlplus connection

Hi, I'm trying to build a shell script that reads a set of accounts from a file. For each account I need to perform a set of sql queries. So I have a loop with a set of sqlplus connections to retrieved my data. Is it possible to have a single sqlplus connection before entering the loop and... (4 Replies)
Discussion started by: lsantacana
4 Replies

7. Shell Programming and Scripting

redirecting oracle sqlplus select query into file

So, I would like to run differen select queries on multiple databases.. I made a script wich I thought to be called something like.. ./script.sh sql_file_name out.log or to enter select statement in a command line.. (aix) and I did created some shell script wich is not working.. it... (6 Replies)
Discussion started by: bongo
6 Replies

8. Shell Programming and Scripting

SQLPLUS query in Unix script

Hi, I am using sqlplus query to get results in a csv format in unix. I am using ksh, and below is the query. echo "select r.num|| ',' || p.path ||',"' || r.issue_description ||'",' ||p.timestamp from events r, messagepath p;">> $QUERY_FILE sqlplus -s $LOGIN @ $QUERY_FILE>>$OUTFILE ... (2 Replies)
Discussion started by: Nutan
2 Replies

9. Shell Programming and Scripting

In a csh script, can I set a variable to the result of an SQLPLUS select query?

Can someone tell me why I'm getting error when I try to run this? #!/bin/csh -f source ~/.cshrc # set SQLPLUS = ${ORACLE_HOME}/bin/sqlplus # set count=`$SQLPLUS -s ${DB_LOGIN} << END select count(1) from put_groups where group_name='PC' and description='EOD_EVENT' and serial_number=1;... (7 Replies)
Discussion started by: gregrobinsonhd
7 Replies

10. UNIX for Advanced & Expert Users

Set shell variables from SQLPLUS query results

Hi All, I needed to get the result of two sqlplus queris into shell variables. After days of looking for the ultimate solution to this problem.. i found this... sqlplus -s USER/PASS@DB <<EOF | awk '{if(NR==1) printf("%s ", $1); if(NR==2) printf("%s ", $1);}' | read VAR1 VAR2 set head off... (2 Replies)
Discussion started by: pranavagarwal
2 Replies
Login or Register to Ask a Question