# SQLPLUS command with more than 1 select statement

**URL:** <https://community.unix.com/t/sqlplus-command-with-more-than-1-select-statement/342662>\
**Category:** Shell Programming and Scripting\
**Created:** [May 13, 2014, 6:34am UTC](https://community.unix.com/t/sqlplus-command-with-more-than-1-select-statement/342662 "2014-05-13T06:34:25Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![Pratiksha\_Mehra](https://community.unix.com/letter_avatar/pratiksha_mehra/32/6_449c533f586939b6e5f246632a46421f.png) [@Pratiksha\_Mehra](https://community.unix.com/u/Pratiksha_Mehra)\
**Post date:** [May 13, 2014, 6:34am UTC](https://community.unix.com/t/sqlplus-command-with-more-than-1-select-statement/342662/1 "2014-05-13T06:34:25Z")

</div>

Hi all,

I'm using below code

```nohighlight
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?

---

<div class="post-metadata">

**Author:** ![Makarand\_Dodmis](https://community.unix.com/letter_avatar/makarand_dodmis/32/6_449c533f586939b6e5f246632a46421f.png) [@Makarand\_Dodmis](https://community.unix.com/u/Makarand_Dodmis)\
**Post date:** [May 13, 2014, 6:43am UTC](https://community.unix.com/t/sqlplus-command-with-more-than-1-select-statement/342662/2 "2014-05-13T06:43:24Z")

</div>

one way is

```nohighlight
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`

```

---

<div class="post-metadata">

**Author:** ![Pratiksha\_Mehra](https://community.unix.com/letter_avatar/pratiksha_mehra/32/6_449c533f586939b6e5f246632a46421f.png) [@Pratiksha\_Mehra](https://community.unix.com/u/Pratiksha_Mehra)\
**Post date:** [May 13, 2014, 6:47am UTC](https://community.unix.com/t/sqlplus-command-with-more-than-1-select-statement/342662/3 "2014-05-13T06:47:13Z")

</div>

cant i do it frm single sqlplus

---

<div class="post-metadata">

**Author:** ![chacko193](https://community.unix.com/letter_avatar/chacko193/32/6_449c533f586939b6e5f246632a46421f.png) [@chacko193](https://community.unix.com/u/chacko193)\
**Post date:** [May 13, 2014, 7:06am UTC](https://community.unix.com/t/sqlplus-command-with-more-than-1-select-statement/342662/4 "2014-05-13T07:06:09Z")

</div>

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

```nohighlight
echo $processId

```

after you have executed sqlplus.

---

<div class="post-metadata">

**Author:** ![Pratiksha\_Mehra](https://community.unix.com/letter_avatar/pratiksha_mehra/32/6_449c533f586939b6e5f246632a46421f.png) [@Pratiksha\_Mehra](https://community.unix.com/u/Pratiksha_Mehra)\
**Post date:** [May 13, 2014, 7:15am UTC](https://community.unix.com/t/sqlplus-command-with-more-than-1-select-statement/342662/5 "2014-05-13T07:15:20Z")

</div>

processId is any number: the output is like:

8742  
N  
these are the values returned by 2 select queries

---

<div class="post-metadata">

**Author:** ![Makarand\_Dodmis](https://community.unix.com/letter_avatar/makarand_dodmis/32/6_449c533f586939b6e5f246632a46421f.png) [@Makarand\_Dodmis](https://community.unix.com/u/Makarand_Dodmis)\
**Post date:** [May 13, 2014, 7:20am UTC](https://community.unix.com/t/sqlplus-command-with-more-than-1-select-statement/342662/6 "2014-05-13T07:20:19Z")

</div>

with single sqlplus try

```nohighlight
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

```nohighlight
Makarand >./mak.sh
Makarand
Dodmis

```

---

<div class="post-metadata">

**Author:** ![Pratiksha\_Mehra](https://community.unix.com/letter_avatar/pratiksha_mehra/32/6_449c533f586939b6e5f246632a46421f.png) [@Pratiksha\_Mehra](https://community.unix.com/u/Pratiksha_Mehra)\
**Post date:** [May 13, 2014, 7:22am UTC](https://community.unix.com/t/sqlplus-command-with-more-than-1-select-statement/342662/7 "2014-05-13T07:22:33Z")

</div>

I thought it can be done using AWK

---

<div class="post-metadata">

**Author:** ![Makarand\_Dodmis](https://community.unix.com/letter_avatar/makarand_dodmis/32/6_449c533f586939b6e5f246632a46421f.png) [@Makarand\_Dodmis](https://community.unix.com/u/Makarand_Dodmis)\
**Post date:** [May 13, 2014, 7:26am UTC](https://community.unix.com/t/sqlplus-command-with-more-than-1-select-statement/342662/8 "2014-05-13T07:26:07Z")

</div>

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

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

---

<div class="post-metadata">

**Author:** ![clx](https://community.unix.com/letter_avatar/clx/32/6_449c533f586939b6e5f246632a46421f.png) [@clx](https://community.unix.com/u/clx)\
**Post date:** [May 13, 2014, 8:24am UTC](https://community.unix.com/t/sqlplus-command-with-more-than-1-select-statement/342662/9 "2014-05-13T08:24:26Z")

</div>

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

```plaintext
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](http://www.akadia.com/services/ora_co_processes.html)