That will not work, because ${arr[j]} will get expanded in the here document, before it is passed to sqlplus, so the j in the SQL loop has nothing to do with the j in the array element..
Try this for running SQL/Plus in a co-process to reduce overhead. I modified an existing example script I had created to illustrate using the co-process. It could use some error handling but you get the idea. For this example it may be overkill, but assuming you want to loop through array elements and perform some processing before inserting, this sets up a framework. Output is logged too:
In SQL/Plus:
SQL> create table test (
2 a integer
3 )
4 ;
Table created.
Code:
#!/bin/ksh
##
## Coprocess example. Start SQL/Plus in the background, and communicate
## with it via a pipe as long as you need it. This saves overhead of
## starting it multiple times. It stays running while we need it.
##
## This example reads an array and inserts a row for each array element.
##
## print -p to send to the coprocess,
## read -p to read from it a line at a time.
##
output="" ## Output from SQL/Plus goes here.
set -f output ## Don't do filename expansion on this variable.
## For when an error from SQL/Plus contains
## a splat (*).
typeset -r LOGFILE=$0.log ## Define a log file for SQL/Plus output.
typeset -r EOF="DONE" ## Text used to indicate the end of
## SQL/Plus output.
set -A test_array 0 1 2 3 4 ## Create and load array.
integer i=0 ## Array element pointer.
## Create the log file or zero it out if it already exists.
> $LOGFILE
## Start sqlplus in a coprocess.
sqlplus -S login/password@database |&
## Exit SQL/Plus if any of the following signals are received:
## 0=normal exit, 2=interrupt, 3=quit, 9=kill, 15=termination
trap 'print -p "exit;"' 0 2 3 9 15
##
## Loop through the array elements, inserting a row for each element.
##
## ${#test_array
[*]} is the number of elements in the array.
##
while (( i < ${#test_array
[*]} ))
do
print "Inserting element $i of ${#test_array
[*]} - Value: ${test_array[$i]}" \
>> $LOGFILE
print -p "insert into test values (${test_array[$i]});"
print -p "prompt $EOF;" ## This is an indicator that we reached the end
## of output. When we read a $EOF from
## the coprocess we know we have no more output.
## Read the output from SQL/Plus a line at a time. When $EOF
## is read, that indicates the end of output.
read -p output # Prime the pump.
while [[ "$output" != "$EOF" ]]
do
print "$output" >> $LOGFILE
read -p output
done
# Increment the array pointer.
(( i = $i + 1 ))
done
# Done reading the array.
print -p "commit;"
# Close the coprocess. Technically this is handled by the trap.
print -p "exit;"
exit 0
After running:
SQL> select * from test;
A
----------
0
1
2
3
4
5 rows selected.
Logfile contents:
$ cat *.log
Inserting element 0 of 5 - Value: 0
1 row created.
Inserting element 1 of 5 - Value: 1
1 row created.
Inserting element 2 of 5 - Value: 2
1 row created.
Inserting element 3 of 5 - Value: 3
1 row created.
Inserting element 4 of 5 - Value: 4
1 row created.
$
The code in post #1 should run in a recent ksh93, but since the the shebang is shifted one character to the right, it will just be treated as a comment and not be used anyway.
I noticed that too but the first line of the desired output example also was shifted over by a space so I figured it was a copy/paste formatting issue.
You bet. FYI - I just realized there is no logging of the commit result. You would want to check that with another loop to read the output of the commit and report if it is not successful. Perhaps put the reading of the output loop in a function? I'll leave that as an exercise.
