Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

SQLPLUS command with more than 1 select statement

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting SQLPLUS command with more than 1 select statement
# 1  
Old 05-13-2014
SQLPLUS command with more than 1 select statement
Hi all,

I'm using below code
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}'
             AND STUDY_NAME = '${studyName}' AND UPPER(STATUS) = 'RUNNING';
       SELECT STRUCTURE_CHANGE FROM LSHADMIN.DATA_DOMAIN WHERE  DOMAIN_NAME =  '${tabname}'
             AND STUDY_NAME = '${studyName}';
       EXIT;
       CHK_PROCESS`

I want the value of first and second select statement seperately in different variables how can i do that?
# 2  
Old 05-13-2014
one way is
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}'
             AND STUDY_NAME = '${studyName}' AND UPPER(STATUS) = 'RUNNING';
       EXIT;
       CHK_PROCESS`
struc_change=`sqlplus -s ${sysuser}/${syspwd} <<CHK_PROCESS
                whenever sqlerror exit sql.sqlcode;
       set head off feedback off echo off pages 0
       SELECT STRUCTURE_CHANGE FROM LSHADMIN.DATA_DOMAIN WHERE  DOMAIN_NAME =  '${tabname}'
             AND STUDY_NAME = '${studyName}';
       EXIT;
       CHK_PROCESS`

# 3  
Old 05-13-2014
cant i do it frm single sqlplus
# 4  
Old 05-13-2014
What is the output form you OP? What does processId contain? Can you show the output for
Code:
echo $processId

after you have executed sqlplus.
# 5  
Old 05-13-2014
processId is any number: the output is like:

8742
N
these are the values returned by 2 select queries
# 6  
Old 05-13-2014
with single sqlplus try
Code:
function run_oracle {
var=`sqlplus -s <<%%
${DB_LOGON}
set serveroutput off
set heading off
set feedback off
set verify off
set define off
set linesize 2000
$1
exit
%%`
}
cmd="select C from C;"
run_oracle "$cmd"
var1=$var
echo $var1
cmd2="select D from C;"
run_oracle "$cmd2"
var2=$var
echo $var2

replace my queries with your queries.
value you get in var1 and var2

output
Code:
Makarand >./mak.sh
Makarand
Dodmis

This User Gave Thanks to Makarand Dodmis For This Post:
Pratiksha Mehra
# 7  
Old 05-13-2014
I thought it can be done using AWK
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

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

2. Shell Programming and Scripting

Use sqlplus statement inside case sentence

Hello, I have a problem. I want to launch a different sql queries for different shell parameter values, something like this. #/bin/bash case $1 in "A") sqlplus -s user/pass << SQL query A; SQL "B") sqlplus -s user/pass << SQL2 ... (3 Replies)
Discussion started by: Vares
3 Replies

3. Shell Programming and Scripting

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: cust_count=`sqlplus -s $ORACLE_USER/$ORACLE_PASS@$ORACLE_SID << EOF set pagesize 0 set feedback off set verify off ... (1 Reply)
Discussion started by: lovelysethii
1 Replies

4. Programming

SQLPlus Case statement on a substring

Hi, I have an SQL query that returns a substring of a field and I want to work a case statement on it, but either I get a syntax error or in it's current manifestation the case is ignored all together. select distinct dbms_lob.substr(msg_body,1,4) as networkId, A_STATUS, count(*) from... (1 Reply)
Discussion started by: chris01010
1 Replies

5. UNIX and Linux Applications

linux sqlplus select results writes into file twice

Hello, This is my first post and its because I could not find solution for myself I decided to ask help here. What I want to do; I want to get some data from a table 1 on server 1 and insert those datas into a table 2 on server 2. ( lets say schema names are server1 and server 2 also ).... (10 Replies)
Discussion started by: azuahaha
10 Replies

6. UNIX for Advanced & Expert Users

SQLPlus Select with Grep

I m trying to grep 'select * from' from a script, but the problem is with the * ... I think its taking * as some special character ... when I try to do grep '*' test (test is my test file) its displaying * But when I am trying to do grep 'select * from' test its not showing anything... (2 Replies)
Discussion started by: RDR
2 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

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

9. UNIX and Linux Applications

Oracle Select IN statement

If I recall, when I used informix I could do a sql statement like: SELECT Value from Table WHERE ID in (100,200,300); How do I do this in Oracle? I believe I am using Oracle 10 if that matters. Thanks. (1 Reply)
Discussion started by: benefactr
1 Replies

10. 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
Login or Register to Ask a Question