# Passing Shell array to SQLPlus

**URL:** <https://community.unix.com/t/passing-shell-array-to-sqlplus/300730>\
**Category:** Shell Programming and Scripting\
**Created:** [December 8, 2011, 2:34am UTC](https://community.unix.com/t/passing-shell-array-to-sqlplus/300730 "2011-12-08T02:34:58Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![roby2411](https://community.unix.com/letter_avatar/roby2411/32/6_22734026db12803ebb3e059622ac3534.png) [@roby2411](https://community.unix.com/u/roby2411)\
**Post date:** [December 8, 2011, 2:34am UTC](https://community.unix.com/t/passing-shell-array-to-sqlplus/300730/1 "2011-12-08T02:34:58Z")

</div>

Dears,  
Newbie here and tried to search this topic for 3 days now with results.  
I have a shell array and I want to use it in sqlplus with one connection.

here is what I have for now

```nohighlight
#!/bin/ksh

FileName[0]=1000
FileName[1]=2000
FileName[2]=3000
FileName[3]=4000

sqlplus /nolog <<EOF
connect user@database/password
set serveroutput on size 1000000
variable deptno number;
exec :deptno := '${FileName[0]}'
select customer_name from customer where customer_id=:deptno;
EOF

```

the above code works fine but with one array index.  
I want too loop in the SQL part and pass the loop parameter to the shell array as I'm imaging below

```nohighlight
#!/bin/ksh

FileName[0]=1000
FileName[1]=2000
FileName[2]=3000
FileName[3]=4000

sqlplus /nolog <<EOF
connect user@database/password
set serveroutput on size 1000000
variable deptno number;

while (x<4)
loop
exec :deptno := '${FileName[x]}'
select customer_name from customer where customer_id=:deptno;
end loop
EOF

```

How can i do this

---

<div class="post-metadata">

**Author:** ![itkamaraj](https://community.unix.com/user_avatar/community.unix.com/itkamaraj/32/1880_2.png) [@itkamaraj](https://community.unix.com/u/itkamaraj)\
**Post date:** [December 8, 2011, 2:57am UTC](https://community.unix.com/t/passing-shell-array-to-sqlplus/300730/2 "2011-12-08T02:57:41Z")

</div>

you need to connect for 4 times.

because for the here document will pass 4 times exec: deptno := \<value\> to the sqlplus.

so, better to put the while loop outside and use sqlplus inside

---

<div class="post-metadata">

**Author:** ![roby2411](https://community.unix.com/letter_avatar/roby2411/32/6_22734026db12803ebb3e059622ac3534.png) [@roby2411](https://community.unix.com/u/roby2411)\
**Post date:** [December 8, 2011, 3:05am UTC](https://community.unix.com/t/passing-shell-array-to-sqlplus/300730/3 "2011-12-08T03:05:36Z")

</div>

thank you itkamaraj for the immediate response, I'm trying to find a way to do it in one connection.  
is it possible to pass the shell array to an sqlplus array and loop on the sqlplus array itself?

---

<div class="post-metadata">

**Author:** ![itkamaraj](https://community.unix.com/user_avatar/community.unix.com/itkamaraj/32/1880_2.png) [@itkamaraj](https://community.unix.com/u/itkamaraj)\
**Post date:** [December 8, 2011, 3:10am UTC](https://community.unix.com/t/passing-shell-array-to-sqlplus/300730/4 "2011-12-08T03:10:13Z")

</div>

instead of that, you can write all queries in a file and execute a file once using sqlplus

---

<div class="post-metadata">

**Author:** ![roby2411](https://community.unix.com/letter_avatar/roby2411/32/6_22734026db12803ebb3e059622ac3534.png) [@roby2411](https://community.unix.com/u/roby2411)\
**Post date:** [December 8, 2011, 3:20am UTC](https://community.unix.com/t/passing-shell-array-to-sqlplus/300730/5 "2011-12-08T03:20:26Z")

</div>

Sorry for pushing, but they are many files that must be parsed in array using advanced grep for each file and then load it to sql.  
Writing them in files mean extra diskspace and better file management as the files is being changed every 5 minutes

---

<div class="post-metadata">

**Author:** ![felipe.vinturin](https://community.unix.com/user_avatar/community.unix.com/felipe.vinturin/32/1840_2.png) [@felipe.vinturin](https://community.unix.com/u/felipe.vinturin)\
**Post date:** [December 8, 2011, 5:47am UTC](https://community.unix.com/t/passing-shell-array-to-sqlplus/300730/6 "2011-12-08T05:47:53Z")

</div>

Check the code below:

```nohighlight
$ cat testScript.sh
buildSQLPlusExecStr ()
{
	unset sqlStr
	
	arrayCountPos=0
	arrayLastPos=${#FileName[@]}
	while [${arrayCountPos} -lt ${arrayLastPos}]
	do
		currValue="${FileName[${arrayCountPos}]}"
		# Do you really need the line below?
		# sqlStr="${sqlStr} \n exec :deptno := '${currValue}'"
		# sqlStr="${sqlStr} \n select customer_name from customer where customer_id=:deptno;"
		
		# Can't you just do this?
		sqlStr="${sqlStr} \n select customer_name from customer where customer_id='${currValue}';"
		arrayCountPos=`expr ${arrayCountPos} + 1`
	done
}

FileName[0]=1000
FileName[1]=2000
FileName[2]=3000
FileName[3]=4000

buildSQLPlusExecStr

echo -e "${sqlStr}"

$ ./testScript.sh
 select customer_name from customer where customer_id='1000';
 select customer_name from customer where customer_id='2000';
 select customer_name from customer where customer_id='3000';
 select customer_name from customer where customer_id='4000';

```

I hope it helps!

---

<div class="post-metadata">

**Author:** ![roby2411](https://community.unix.com/letter_avatar/roby2411/32/6_22734026db12803ebb3e059622ac3534.png) [@roby2411](https://community.unix.com/u/roby2411)\
**Post date:** [December 8, 2011, 6:36am UTC](https://community.unix.com/t/passing-shell-array-to-sqlplus/300730/7 "2011-12-08T06:36:57Z")

</div>

Hi Felipe,  
every line of code helps, I'm learning from great ideas that are being shared here.  
May be it is my mistake that I didn't clear my point earlier but I'll try to explain more my issue

I have a system that is throwing number of id's in files where each file has more than one line and each line has one id  
right now i have a code to cat the files each 5 minute and pass each file content to the given array FileName -the array in my code is just sample for the dynamic output and it is not static like shown- and i do shell loop to do the following

1. select the next value from shell array using the loop number as index
2. open sql connection
3. run query using sqlplus bind variable and query about the given value from shell array
4. close the connection

all what i'm looking for is to keep the whole logic but loop after opening the sqlplus because this change will offload the oracle resources somehow as opening connection is considered a high cost action for resources

I hope that helps to understand my objective, thank you in advance for the kind support and help you do here.

---

<div class="post-metadata">

**Author:** ![felipe.vinturin](https://community.unix.com/user_avatar/community.unix.com/felipe.vinturin/32/1840_2.png) [@felipe.vinturin](https://community.unix.com/u/felipe.vinturin)\
**Post date:** [December 8, 2011, 6:57am UTC](https://community.unix.com/t/passing-shell-array-to-sqlplus/300730/8 "2011-12-08T06:57:29Z")

</div>

It is still not clear to me what you want, but let see: you want to execute sqlplus for every Id you find, is that right?

```nohighlight
$ cat testScript.sh
execSQLPlus ()
{
	deptNo="${1}"
	sqlplus -S -L /nolog <<EOF
		whenever sqlerror exit 1
		connect user@database/password
		set serveroutput on size 1000000
		variable deptno number;
		exec :deptno := '${deptNo}'
		select customer_name from customer where customer_id=:deptno;
EOF
	sqlplusRetCode=$?
	if [${sqlplusRetCode} -ne 0]
	then
		echo "Failed to execute query for deptno: [${deptNo}]. Return code: [${sqlplusRetCode}]."
		return ${sqlplusRetCode}
	fi
	return 0
}

arrayCountPos=0
arrayLastPos=${#FileName[@]}
while [${arrayCountPos} -lt ${arrayLastPos}]
do
	currValue="${FileName[${arrayCountPos}]}"
	
	execSQLPlus "${currValue}"
	
	arrayCountPos=`expr ${arrayCountPos} + 1`
done

```

I don't have how to test it now, but it should work!

---

<div class="post-metadata">

**Author:** ![roby2411](https://community.unix.com/letter_avatar/roby2411/32/6_22734026db12803ebb3e059622ac3534.png) [@roby2411](https://community.unix.com/u/roby2411)\
**Post date:** [December 8, 2011, 7:42am UTC](https://community.unix.com/t/passing-shell-array-to-sqlplus/300730/9 "2011-12-08T07:42:27Z")

</div>

That's too much close to what I'm looking for,  
The only point left is i do need to open the connection one time for each array

is it possible to wave  
connect user@database/password  
to be connected before the loop starts, and close the connection after the whole loop ends

---

<div class="post-metadata">

**Author:** ![felipe.vinturin](https://community.unix.com/user_avatar/community.unix.com/felipe.vinturin/32/1840_2.png) [@felipe.vinturin](https://community.unix.com/u/felipe.vinturin)\
**Post date:** [December 8, 2011, 7:57am UTC](https://community.unix.com/t/passing-shell-array-to-sqlplus/300730/10 "2011-12-08T07:57:33Z")

</div>

In shell, I don't know a way to keep an Oracle connection opened (and I don't think there is a way, sqlplus does not support it!).

By the way, if you want this, why the first piece of code I wrote is not valid to you? It executes all queries in the FileName array sequentially, in the same connection!

If you really need to open the connection once, execute the queries and then close the connection, I suggest you to use another programming language.

My suggestion is: [Python](http://python.org/) and its lib to connect to Oracle: [cx\_Oracle](http://pypi.python.org/pypi/cx_Oracle)

Here is an example: [Mastering Oracle+Python, Part 1: Querying Best Practices](http://www.oracle.com/technetwork/articles/prez-python-queries-101587.html)

---

<div class="post-metadata">

**Author:** ![roby2411](https://community.unix.com/letter_avatar/roby2411/32/6_22734026db12803ebb3e059622ac3534.png) [@roby2411](https://community.unix.com/u/roby2411)\
**Post date:** [December 8, 2011, 8:44am UTC](https://community.unix.com/t/passing-shell-array-to-sqlplus/300730/11 "2011-12-08T08:44:08Z")

</div>

Sorry but it is not clear to me at which point in the first code you are opening the connection.  
Kindly repeat it marking the line for opening the connection.

---

<div class="post-metadata">

**Author:** ![felipe.vinturin](https://community.unix.com/user_avatar/community.unix.com/felipe.vinturin/32/1840_2.png) [@felipe.vinturin](https://community.unix.com/u/felipe.vinturin)\
**Post date:** [December 8, 2011, 9:58am UTC](https://community.unix.com/t/passing-shell-array-to-sqlplus/300730/12 "2011-12-08T09:58:54Z")

</div>

What is your OS and what shell are you using?

---

<div class="post-metadata">

**Author:** ![durden\_tyler](https://community.unix.com/user_avatar/community.unix.com/durden_tyler/32/1450_2.png) [@durden\_tyler](https://community.unix.com/u/durden_tyler)\
**Post date:** [December 8, 2011, 11:51am UTC](https://community.unix.com/t/passing-shell-array-to-sqlplus/300730/13 "2011-12-08T11:51:11Z")

</div>

> [@roby2411](#):
>
> ...  
> I have a system that is throwing number of id's in files where each file has more than one line and each line has one id  
> right now i have a code to cat the files each 5 minute and pass each file content to the given array FileName -the array in my code is just sample for the dynamic output and it is not static like shown- and i do shell loop to do the following
> 
> 1. select the next value from shell array using the loop number as index
> 2. open sql connection
> 3. run query using sqlplus bind variable and query about the given value from shell array
> 4. close the connection
> 
> all what i'm looking for is to keep the whole logic but loop after opening the sqlplus because this change will offload the oracle resources somehow as opening connection is considered a high cost action for resources  
> ...

You could loop through the array, generate all SQL statements, assign that to a shell variable and then use that variable in the sqlplus connection.  
Thus, you move the task of generating your statements to Unix and open only one connection to Oracle when you're ready with your batch of queries.

```plaintext
$
$
$ cat -n oraconnect.sh
  1 #!/usr/bin/bash
  2
  3 # Let's say the array has been populated at this point
  4 # Over here, I've populated the empno values from the EMP
  5 # demonstration table of the SCOTT sample schema of Oracle
  6 id_array[1]=7369
  7 id_array[2]=7839
  8 id_array[3]=7900
  9 id_array[4]=7654
 10
 11 # Generate all SQL statements by looping through the array
 12 # and assign that to a single shell variable
 13 SQLSTR=$(for i in "${id_array[@]}"; do
 14 echo "SELECT ename from emp where empno = $i;"'\n'
 15 done)
 16
 17 # now connect to Oracle and execute the SQL statements in
 18 # the string variable
 19 sqlplus -s /nolog <<EOF
 20 connect user/password@db
 21 set pages 0 heading off time off timing off feed off
 22 $(echo -e $SQLSTR)
 23 exit
 24 EOF
 25
$
$
$ ./oraconnect.sh
SMITH
KING
JAMES
MARTIN
$
$
$

```

Now for the part that says that the array is dynamic and is actually being created by reading text files. In that case, you do not need an array.  
You loop through the files, read the ids, generate the queries all the while appending them, assign the value to a Unix variable and then use it while interacting with Oracle.

```plaintext
$
$
$ # The Ids are in the files - file1 and file2
$
$ cat file1
7369
7839
$
$ cat file2
7900
7654
$
$
$ cat file1 file2
7369
7839
7900
7654
$
$
$ cat -n oraconnect1.sh
  1 #!/usr/bin/bash
  2
  3 # If the Ids are being fetched from files that have one Id
  4 # per line, then an array is not really needed.
  5 SQLSTR=$(cat file? | while read i; do
  6 echo "SELECT ename from emp where empno = $i;"'\n'
  7 done)
  8
  9 # now connect to Oracle and execute the SQL statements in
 10 # the string variable
 11 sqlplus -s /nolog <<EOF
 12 connect user/password@db
 13 set pages 0 heading off time off timing off feed off
 14 $(echo -e $SQLSTR)
 15 exit
 16 EOF
 17
$
$
$ ./oraconnect1.sh
SMITH
KING
JAMES
MARTIN
$
$
$

```

tyler\_durden

---------- Post updated at 11:51 AM ---------- Previous update was at 11:28 AM ----------

In fact, depending on what your queries are and how you want to use their output, you might be able to use the IN operator, like so -

```plaintext
$
$ # For the text files - "file1" and "file2" posted earlier...
$
$ cat -n oraconnect2.sh
  1 #!/usr/bin/bash
  2
  3 # If the Ids are being fetched from files that have one Id
  4 # per line, then an array is not really needed.
  5 SQLSTR=$(cat file? |
  6 xargs printf "%s, " |
  7 sed 's/^\(.*\), $/SELECT ename FROM emp WHERE empno IN (\1);/')
  8
  9 echo "My query is: $SQLSTR"
 10
 11 # now connect to Oracle and execute the SQL statements in
 12 # the string variable
 13 sqlplus -s /nolog <<EOF
 14 connect user/password@db
 15 set pages 0 heading off time off timing off feed off
 16 $(echo -e $SQLSTR)
 17 exit
 18 EOF
 19
$
$
$ ./oraconnect2.sh
My query is: SELECT ename FROM emp WHERE empno IN (7369, 7839, 7900, 7654);
SMITH
MARTIN
KING
JAMES
$
$

```

tyler\_durden

---

<div class="post-metadata">

**Author:** ![roby2411](https://community.unix.com/letter_avatar/roby2411/32/6_22734026db12803ebb3e059622ac3534.png) [@roby2411](https://community.unix.com/u/roby2411)\
**Post date:** [December 11, 2011, 7:24am UTC](https://community.unix.com/t/passing-shell-array-to-sqlplus/300730/14 "2011-12-11T07:24:31Z")

</div>

Thank you durden  
the last part of code is what I was looking for, but there is another issue now. using this way will use the sql bind variables.

I still need to use bind variables from oracle to execute a pre defined shell array  
And sorry for the delay in answering and give feedback

---

<div class="post-metadata">

**Author:** ![durden\_tyler](https://community.unix.com/user_avatar/community.unix.com/durden_tyler/32/1450_2.png) [@durden\_tyler](https://community.unix.com/u/durden_tyler)\
**Post date:** [December 12, 2011, 10:18am UTC](https://community.unix.com/t/passing-shell-array-to-sqlplus/300730/15 "2011-12-12T10:18:52Z")

</div>

> [@roby2411](#):
>
> ...but there is another issue now. using this way will use the sql bind variables.  
> I still need to use bind variables from oracle to execute a pre defined shell array...

So what's the issue?

tyler\_durden

---

<div class="post-metadata">

**Author:** ![roby2411](https://community.unix.com/letter_avatar/roby2411/32/6_22734026db12803ebb3e059622ac3534.png) [@roby2411](https://community.unix.com/u/roby2411)\
**Post date:** [December 25, 2011, 6:21am UTC](https://community.unix.com/t/passing-shell-array-to-sqlplus/300730/16 "2011-12-25T06:21:42Z")

</div>

Using this way  
echo "SELECT ename from emp where empno = $i;"'\n'will pass the empno as value, I was looking to pass it as sql bind variable which looks like  
echo "SELECT ename from emp where empno = :SomeId;"'\n'

---

<div class="post-metadata">

**Author:** ![durden\_tyler](https://community.unix.com/user_avatar/community.unix.com/durden_tyler/32/1450_2.png) [@durden\_tyler](https://community.unix.com/u/durden_tyler)\
**Post date:** [December 26, 2011, 12:10pm UTC](https://community.unix.com/t/passing-shell-array-to-sqlplus/300730/17 "2011-12-26T12:10:16Z")

</div>

> [@roby2411](#):
>
> Using this way  
> echo "SELECT ename from emp where empno = $i;"'\n'will pass the empno as value, I was looking to pass it as sql bind variable which looks like  
> echo "SELECT ename from emp where empno = :SomeId;"'\n'

Why do you want to do that?

tyler\_durden

---

<div class="post-metadata">

**Author:** ![binlib](https://community.unix.com/letter_avatar/binlib/32/6_22734026db12803ebb3e059622ac3534.png) [@binlib](https://community.unix.com/u/binlib)\
**Post date:** [December 26, 2011, 2:32pm UTC](https://community.unix.com/t/passing-shell-array-to-sqlplus/300730/18 "2011-12-26T14:32:52Z")

</div>

...

```nohighlight
while (x<4)
loop
exec :deptno := '${FileName[x]}'
select customer_name from customer where customer_id=:deptno;
end loop

```

You almost got it, just need a sub shell in the here doc:

```nohighlight
$(for i in ${FileName[@]}; do
echo "
exec :deptno := '$i'
select customer_name from customer where customer_id = :deptno;
"
done)

```

You may need to change $() to `` to get around of a ksh bug.

---

<div class="post-metadata">

**Author:** ![roby2411](https://community.unix.com/letter_avatar/roby2411/32/6_22734026db12803ebb3e059622ac3534.png) [@roby2411](https://community.unix.com/u/roby2411)\
**Post date:** [December 27, 2011, 6:56am UTC](https://community.unix.com/t/passing-shell-array-to-sqlplus/300730/19 "2011-12-27T06:56:38Z")

</div>

Tyler\_durden, this is requested by the DBA team, using bind Variable is preferred in continuous operation that user variable [Bind Variables](http://www.adp-gmbh.ch/ora/concepts/bind_variables.html)(I'm posting the link to share the information that might help others 🙂 ).

Binlib, using your way is by passing the usage of bind SQL variable and all query that are statically using ( yyy=:xxx ) will be rejected as :xxx is a non accepted value for column yyy

At the end, please let me say thank you for all those are helping here, I'm really learning from your great experiences and new information were added to my knowledge with each single line of code you dropped here to help me.

---------- Post updated at 06:56 AM ---------- Previous update was at 06:47 AM ----------

For those where are not familiar with bin variables this article simply explain why you might use them one time.  
[Bind variables - The key to application performance](http://www.akadia.com/services/ora_bind_variables.html)

---

<div class="post-metadata">

**Author:** ![cero](https://community.unix.com/letter_avatar/cero/32/6_22734026db12803ebb3e059622ac3534.png) [@cero](https://community.unix.com/u/cero)\
**Post date:** [December 27, 2011, 8:27am UTC](https://community.unix.com/t/passing-shell-array-to-sqlplus/300730/20 "2011-12-27T08:27:06Z")

</div>

Shell variables can not be used as bind-variables directly. A bind variable is a variable of the accessing application passed to the database. The accessing application in this case is SQL_Plus and not the shell itself. You can use bind variables defined in SQL_Plus as shown in several places in this thread.  
Your DBA team gives good advice about bind variables, but there is no way to prepare the statement and then pass the variables when the prepared statement is executed because the shell does not have a mechanism to do so.  
Perl or phyton offer support for bind variables and do not need SQL\*Plus to connect to the database.

[Next page](https://community.unix.com/t/passing-shell-array-to-sqlplus/300730.md?page=2)