Using unix array in sql


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Using unix array in sql
# 1  
Old 06-24-2012
Using unix array in sql

Hi all,

I have declared an array in unix as below.
Code:
 #!/bin/ksh
arr=()
for i in {0..4}
do
    arr[i]=$i;
done

.

i want to insert these array variables into the oracle table.can you guys please help me in doing this. my code is as below.
Code:
value=`sqlplus -s fos/fos << EOF

begin
for j in 0..4 loop
insert into test values ('${arr[j]}');
end loop;
end;
/
exit;
EOF`

its inserting 5 rows with value 4.
but it should insert
Code:
 0
1
2
3
4

.

Moderator's Comments:
Mod Comment edit by bakunin: Please view this code tag video for how to use code tags when posting code and data. Why you consistently used "[" and "]" instead of the tags eludes me.


---------- Post updated 06-24-12 at 02:11 AM ---------- Previous update was 06-23-12 at 12:41 PM ----------

hi guys,
please help me i need it badly.

Last edited by bakunin; 06-23-2012 at 03:43 PM..
# 2  
Old 06-24-2012
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..
# 3  
Old 06-24-2012
so can you please tell me what should i do to make it work?
# 4  
Old 06-24-2012
Try something like this:

Code:
#!/bin/ksh
arr= ( $(seq 0 4) )
value=$( (
   printf "begin\n"
   printf "insert into test values ('%s');\n" "${arr[@]}"
   printf "end;\nexit;\n") | sqlplus -s fos/fos )

---------- Post updated at 09:43 AM ---------- Previous update was at 09:22 AM ----------

BTW: the brace expansion {0..4} you used to create your array is a bash thing, and will not work in ksh you need to use $(seq 0 4) for ksh.

Last edited by Chubler_XL; 06-24-2012 at 08:31 PM..
This User Gave Thanks to Chubler_XL For This Post:
# 5  
Old 06-25-2012
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:
Code:
SQL> create table test (
  2  a integer
  3  )
  4  ;

Table created.

Code:
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:
Code:
SQL> select * from test;

         A
----------
         0
         1
         2
         3
         4

5 rows selected.

Logfile contents:
Code:
$ 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.

$

These 2 Users Gave Thanks to gary_w For This Post:
# 6  
Old 06-25-2012
Nice clean example of using coprocesses gary_w.

Just hope OP is using ksh, as posted code is bash but with " #!/bin/ksh" at the top.
# 7  
Old 06-25-2012
Thanks. Yeah I noticed that. Hopefully the OP will reply with what worked.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Problem in passing IFS array to SQL Query

Hi, I have created a shell script that reads line from text file and insert into DB table. I have used IFS to separate the line text. Looks IFS is splitting text properly but while passing one of the values that has special characters in it to query, it is giving weird issue. Below is my... (2 Replies)
Discussion started by: yuvi
2 Replies

2. Shell Programming and Scripting

Storing the SQL results in array variables

Requirement 1) I need to execute 15 SQL queries in oracle through linux script. All these query results needs to be stored in array variables. Requirement 2) And these 15 queries needs to be executed in parallel. Requirement 3) Once all the queries executed then the shell script should... (3 Replies)
Discussion started by: Niranjancse
3 Replies

3. Shell Programming and Scripting

error while updating rows in sql with values in array variable

Hi, I need to update rows in a table based on the values in an array variable. code is : while read line do error_msg="$(echo $line)" index=`expr $index+1` done <"logs/$ffile" rows_count=${#error_msg } i=0 while do echo "error msgs is... (2 Replies)
Discussion started by: RP09
2 Replies

4. Shell Programming and Scripting

Pass perl array to SQL oracle

Hello, Could someone please suggest if there is a way to pass an perl array(pass @v_array) to SQL as in below eg : #!/usr/bin/perl @v_array = (1,2,4,5,6,8); $db_userid = 'ni71/ni711'; $bufTPO = qx{ sqlplus -s << EOF $db_userid set verify off set feedback off set... (1 Reply)
Discussion started by: arunshankar.c
1 Replies

5. UNIX and Linux Applications

SQL database call into Multidimensional Array using Perl Script

#!/usr/local/bin/perl use DBI; use File::Copy; use Time::Local; use Data::Dumper; -Comments Describing what I'm doing-------------- -I'm pulling information from a database that has an ID and Name. They are separated by a space and I'm trying to load them into a multidimensional array so as... (3 Replies)
Discussion started by: eazyeddie22
3 Replies

6. Shell Programming and Scripting

PERL : SQL array bind issue while grouping

Hi, I have an array of strings. Each string has 4 comma separated values. I am binding this array to a SQL where I am required to do an INSERT after grouping. The binding is done as : $insertADWSth->bind_param_array(1,A_CONSTANT_STRING);... (1 Reply)
Discussion started by: sinpeak
1 Replies

7. Shell Programming and Scripting

PERL : Bind 2D array to SQL

Hi, I am in the need of doing a bulk insert via : SQL - INSERT INTO <table> (SELECT..) OR PLSQL Block - FORALL i IN 1 .. count INSERT INTO <table>(arrayname(i)) I have a 2D array in my perl code which has the rows to be bulk inserted. Is there a way to bind the 2D array to the SQL... (4 Replies)
Discussion started by: sinpeak
4 Replies

8. UNIX for Dummies Questions & Answers

Execute PL/SQL function from Unix script (.sql file)

Hi guys, I am new on here, I have a function in oracle that returns a specific value: create or replace PACKAGE BODY "CTC_ASDGET_SCHED" AS FUNCTION FN_ASDSCHEDULE_GET RETURN VARCHAR2 AS BEGIN DECLARE ASDSchedule varchar2(6); ASDComplete... (1 Reply)
Discussion started by: reptile
1 Replies

9. Shell Programming and Scripting

unix variables from sql / pl/sql

How do I dynamically assign the below output to unix shell variables so I can build a menu in a shell script? Example: var1 = 1 var2= SYSTEM var3 = 2 var4= UNDOTBS1 and so on, then in the shell script I can use the variables to build a menu. set serveroutput on declare... (2 Replies)
Discussion started by: djehres
2 Replies

10. UNIX for Advanced & Expert Users

sql variable as array index

hi folks i am facing problom while trying to access sql variable as array index ina unix shell script....script goes as below.. #!/bin/ksh MAX=3 for elem in alpha beeta gaama do arr=$elem ((x=x+1)) Done SQL_SERVER='servername' /apps/sun5/utils/sqsh -S $SQL_SERVER -U user -P pwd -b -h... (1 Reply)
Discussion started by: sudheer157
1 Replies
Login or Register to Ask a Question