SQLPLUS command with more than 1 select statement

Shell Programming and Scripting
1

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}'
             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

one way is

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

cant i do it frm single sqlplus

4

What is the output form you OP? What does processId contain? Can you show the output for

echo $processId

after you have executed sqlplus.

5

processId is any number: the output is like:

8742
N
these are the values returned by 2 select queries

6

with single sqlplus try

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

Makarand >./mak.sh
Makarand
Dodmis
1 Like
7

I thought it can be done using AWK

8

it can be if your output of both select queries are fixed.
let's say first select query returns "Makarand".
and second select query returns "Dodmis".
then below code will work

var=`sqlplus -s ${DB_LOGON}<< EOSQL
WHENEVER SQLERROR EXIT FAILURE;
WHENEVER OSERROR EXIT FAILURE;
set longchunksize 200000 long 200000 pages 0
set heading off
define OutDir  = mak
select C from C;
select D from C;
exit
EOSQL`
var1=`echo $var | awk '{print $1}'`
var2=`echo $var | awk '{print $2}'`
echo $var1
echo $var2

but generaly select query output we cant guess hance i send generic code that will run for all queries.

9

Alternatively you can also use the ksh co-process with that you don't need to re-connect again. And you can execute multiple queries with the same connection.

Sample

e.g.
# Open pipe to Oracle sqlplus
sqlplus -s ${DB_LOGON} |&

# Output variable
OUT=

print -p "WHENEVER SQLERROR EXIT FAILURE;"
print -p "WHENEVER OSERROR EXIT FAILURE;"
print -p "set longchunksize 200000 long 200000 pages 0"
print -p "SET heading Off;"
print -p "define OutDir  = mak"


# Query2
print -p "select C from C;"
print -p "prompt FINISH"

while read -p OUT
do
   if [[ "$OUT" == "FINISH" ]]; then
     break
   else
     echo $OUT  # or do whatever
   fi
done

# Query2
print -p "select D from C;"
print -p "prompt FINISH"

while read -p OUT
do
   if [[ "$OUT" == "FINISH" ]]; then
     break
   else
     echo $OUT  # or do whatever
	 
   fi
done


# Close sqlplus NOW
print -p "exit"

Note : I havn't tested the snippet.

You can find a quick tutorial here