Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Connecting sqlplus from UNIX with multiple select statement

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Connecting sqlplus from UNIX with multiple select statement
# 1  
Old 03-01-2013
Connecting sqlplus from UNIX with multiple select statement
hi,
i have a requirement where i need to connect sqlplus from unix and i am able to do so by following command:
Code:
cust_count=`sqlplus -s $ORACLE_USER/$ORACLE_PASS@$ORACLE_SID   << EOF
                set pagesize 0
                set feedback off
                set verify off
                set heading off
                select count(*) from BCHISTEVENT b, bcuser u
                where b.type != 'com.avolent.apps.event.LogoutEvent'
                and b.actinguserid = u.identifier
                and u.type = 'CustomerView'
                /*and (to_char(b.createdt, 'dd/mm/yy hh24:mi:ss') between
                to_char((SYSTIMESTAMP - INTERVAL '30' MINUTE),'dd/mm/yy hh24:mi:ss') and to_char(SYSTIMESTAMP,'dd/mm/yy hh24:
mi:ss'))*/
                AND b.bucket = to_char(sysdate, 'YYYYMM')
                group by u.type;
EOF`


but i have one more select statement here and if i am using the same above command then i am getting no output while echoing the variabe value:
Code:
biller_count=`sqlplus -s $ORACLE_USER/$ORACLE_PASS@$ORACLE_SID   << EOF
                set pagesize 0
                set feedback off
                set verify off
                set heading off
                select count(*) from BCHISTEVENT b, bcuser u
                where b.type != 'com.avolent.apps.event.LogoutEvent'
                and b.actinguserid = u.identifier
                and u.type = ' BillerAdminView'
                /*and (to_char(b.createdt, 'dd/mm/yy hh24:mi:ss') between
                to_char((SYSTIMESTAMP - INTERVAL '30' MINUTE),'dd/mm/yy hh24:mi:ss') and to_char(SYSTIMESTAMP,'dd/mm/yy hh24:
mi:ss'))*/
                AND b.bucket = to_char(sysdate, 'YYYYMM')
                group by u.type;
EOF`


echo "Total # of users Logged in @ last 30 minutes . Customer View    :$cust_count">>vmstat.txt;
echo "Total # of users Logged in @ last 30 minutes . Biller View      :$biller_count">>vmstat.txt;


plss suggest how to acheive this....thanks in advance
Last edited by Franklin52; 03-01-2013 at 04:10 AM.. Reason: Please use code tags for data and code samples
# 2  
Old 03-01-2013
When you put a multi-line comment in sql always make sure that /* is followed by a space or newline else you will have unexpected results.

So it should be:
Code:
/* and (to_char(b.createdt, 'dd/mm/yy hh24:mi:ss') between

OR
Code:
/*
and (to_char(b.createdt, 'dd/mm/yy hh24:mi:ss') between

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

UNIX Sqlplus - Capture the sql statement about to run and execution status

Greetings Experts, I am on AIX using ksh. Created a unix script which generates the CREATE OR REPLACE VIEW ... and GRANT .. statements, which are placed in a single .txt file. Now I need to execute the contents in the file (there are around 300 view creation and grant statements) in Oracle and... (4 Replies)
Discussion started by: chill3chee
4 Replies

2. Shell Programming and Scripting

SQLPLUS command with more than 1 select statement

Hi all, I'm using below code processId=`sqlplus -s ${sysuser}/${syspwd} <<CHK_PROCESS whenever sqlerror exit sql.sqlcode; set head off feedback off echo off pages 0 SELECT PROCESS_ID FROM LSHADMIN.DATA_DOMAIN WHERE DOMAIN_NAME = '${tabname}' ... (8 Replies)
Discussion started by: Pratiksha Mehra
8 Replies

3. Shell Programming and Scripting

connecting to table to extract multiple rows into file from unix script

I need to extract the data from oracle table and written the below code. But it is not working.There is some problem with the query and output is shown is No rows selected" . If I run the same query from sql developer there is my required output. And if I run the shell script with simple sql... (7 Replies)
Discussion started by: giridhar276
7 Replies

4. UNIX for Advanced & Expert Users

Connecting once using sqlplus and doing multiple queries

Hello everyone, It's my first week using unix and shell scripting. I tried creating a script that has a function that execute SQL query. my script looks something like this: ---------------------------------------------------- #!/bin/sh tableName="myTable" secondTable="secondTable"... (2 Replies)
Discussion started by: edlin_r
2 Replies

5. Shell Programming and Scripting

Connecting to Oracle DB using sqlplus

Hi, I am very new to shell scripting and trying to write a simple shell script in which i am trying to achieve the following: 1. Connect to oracle database hosted on a different server 2. fire a query on the oracle db 3. store the output in a variable 4. use this variable for further logic... (26 Replies)
Discussion started by: shrutihardas
26 Replies

6. UNIX for Dummies Questions & Answers

Connecting to Oracle DB using sqlplus

Hi, I am very new to shell scripting and trying to write a simple shell script in which i am trying to achieve the following: 1. Connect to oracle database hosted on a different server 2. fire a query on the oracle db 3. store the output in a variable 4. use this variable for further logic... (1 Reply)
Discussion started by: shrutihardas
1 Replies

7. Shell Programming and Scripting

connecting through sqlplus

I am trying to connect to one of the oracle sever using uni through sqlplus command: sqlplus -s BOXI_ALPH_AUDITOR,Q078_audit$@Q047 But its not getting connected. I tried using some different server using same syntax its working. What differene i found is the password is having no special... (2 Replies)
Discussion started by: gander_ss
2 Replies

8. UNIX for Dummies Questions & Answers

select count(*) in sqlplus into variable unix shell

Need to select count(*) from table to check for zero result in unix script (2 Replies)
Discussion started by: struggle
2 Replies

9. Windows & DOS: Issues & Discussions

Want to use the output of Select statement in Unix script

Hi, I have a UNIX script which calls SQL Select statement: Now i want to use the output of that select statement within my UNIX script so as to call different shell script depending upon the output of the select statement. Can anyone help me in this regard. TIA Akhil Goel (4 Replies)
Discussion started by: akhilgoel9
4 Replies

10. UNIX for Dummies Questions & Answers

error connecting to sqlplus

Hi, I wrote a shell script to call oracle procedure. But when i am trying to connet sqlplus with the fallowing statement It is giving me error " callproce.sh : sqlplus: not found". What could be the problem. sqlplus -s $CONNECT_STRING >$LOGFILE <<!! thank u all papachi (2 Replies)
Discussion started by: papachi
2 Replies
Login or Register to Ask a Question