Login or Register to Ask a Question and Join Our Community

Sponsored Content
Full Discussion: SQLPLUS unable to execte query automatically from solaris script
Operating Systems Solaris SQLPLUS unable to execte query automatically from solaris script Post 302223492 by jyotisree on Sunday 10th of August 2008 05:46:05 PM
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
 

10 More Discussions You Might Find Interesting

1. 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

2. 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

3. 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

4. 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

5. 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

6. 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

7. 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

8. 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

9. 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

10. 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

LEARN ABOUT DEBIAN

colorgccrc

COLORGCCRC(5)							File Formats Manual						     COLORGCCRC(5)

NAME

       colorgccrc - configuration file for colorgcc

DESCRIPTION

       A colorgccrc configuration file is used to configure the highlighting of the compiler output from colorgcc.

SYNTAX

       Each  line  consists  of  a  keyword  designating  a configuration variable.  The keyword is followed by `:' and then one or several values
       (depending on the keyword).  Lines beginning with a hash mark `#' are comments.

CONFIGURATION VARIABLES

       g++ | gcc | c++ | cc | g77 | gcj | gnat | gpc
	      Specifies the paths to the compilers.  Takes one value; a path to the compiler.

       nocolor
	      Specifies what terminal types colorization should be disabled on.  Takes one or several values, separated by whitespace.

       srcColor
	      Specifies the highlighting attributes source-code should be given.  Takes one or several color attributes.  See  the  section  COLOR
	      ATTRIBUTES for more information.

       introColor
	      Specifies  the  highlighting  attributes	for normal compiler output.  Takes one or several color attributes.  See the section COLOR
	      ATTRIBUTES for more information.

       warningFileNameColor | errorFileNameColor
	      Specifies the highlighting attributes for the filename in a  warning  or	an  error,  respectively.   Takes  one	or  several  color
	      attributes.  See the section COLOR ATTRIBUTES for more information.

       warningNumberColor | errorNumberColor
	      Specifies  the  highlighting  attributes	for  the  line-number  in a warning or an error, respectively.	Takes one or several color
	      attributes.  See the section COLOR ATTRIBUTES for more information.

       warningMessageColor | errorMessageColor
	      Specifies the highlighting attributes for the message-text in a warning or an error,  respectively.   Takes  one	or  several  color
	      attributes.  See the section COLOR ATTRIBUTES for more information.

COLOR ATTRIBUTES

       The following attributes are valid for highlighting.

       clear, reset

       bold, underline, underscore, blink, reverse, concealed

       black, red, green, yellow, blue, magenta, cyan, white

       on_black, on_red, on_green, on_yellow, on_blue, on_magenta, on_cyan, on_white

SEE ALSO

       gcc(1), colorgcc(1)

HISTORY

       Jan 15 2003: Initial version of this manual-page.

REPORTING BUGS

       Report bugs to <jmoyers@geeks.com>

AUTHORS

       Jamie Moyers <jmoyers@geeks.com> is the author of colorgcc.

       This  manual  page  was	written  by Joe Wreschnig <piman@sacredchao.net>, and modified by David Weinehall <tao@debian.org>, for the Debian
       GNU/Linux system (but may be used by others).

COPYRIGHT

       Copyright (C) 2003 Jamie Moyers
       This is free software; see the source for copying conditions.  There is NO warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICU-
       LAR PURPOSE.

								   Jan 15, 2003 						     COLORGCCRC(5)
All times are GMT -4. The time now is 03:19 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy