Passing Shell array to SQLPlus

Shell Programming and Scripting
1

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

#!/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

#!/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

2

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

3

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?

4

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

5

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

6

Check the code below:

$ 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!

7

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.

8

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?

$ 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!

9

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

10

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 and its lib to connect to Oracle: cx_Oracle

Here is an example: Mastering Oracle+Python, Part 1: Querying Best Practices

11

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.

12

What is your OS and what shell are you using?

13

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.

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

$
$
$ # 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 -

$
$ # 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

14

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

15

So what's the issue?

tyler_durden

16

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'

17

Why do you want to do that?

tyler_durden

18

...

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:

$(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.

19

Tyler_durden, this is requested by the DBA team, using bind Variable is preferred in continuous operation that user variable Bind Variables (I'm posting the link to share the information that might help others :slight_smile: ).

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

20

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 SQLPlus and not the shell itself. You can use bind variables defined in SQLPlus 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.