Shell Programming and Scripting

sqlplus -s $user/$pass@$db << END
-- ${colarray[0]} can be used for comparison here --
END

test@XE> 
test@XE> set lin 80
test@XE> 
test@XE> desc t
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 X                                                  VARCHAR2(1)
 Y                                                  VARCHAR2(30)

test@XE> 
test@XE> select * from t;

X Y
- ------------------------------
a this row has "a"
b this row has "b"
c this row has "c"
d this row has "d"
e this row has "e"
f this row has "f"

6 rows selected.

test@XE> 
test@XE>

$ 
$ cat -n f8.sh
     1  #!/bin/bash
     2  colarray[0]="a"
     3  colarray[1]="b"
     4  colarray[2]="c"
     5  # I want to fetch all rows from the Oracle table "T"
     6  # that has values in column "x" equal to any element of "colarray"
     7  # i.e. => select * from t where x in ('a','b','c');
     8  sqlplus -s /nolog << EOF
     9  set feed 1
    10  connect test/test
    11  select * from t;
    12  select * from t where x in (`echo ${colarray[@]} | sed -e "s/ /', '/g" -e "s/^/'/" -e "s/$/'/"`);
    13  exit
    14  EOF
    15
$ 
$

$ 
$ . f8.sh

X Y
- ------------------------------
a this row has "a"
b this row has "b"
c this row has "c"
d this row has "d"
e this row has "e"
f this row has "f"

6 rows selected.


X Y
- ------------------------------
a this row has "a"
b this row has "b"
c this row has "c"

3 rows selected.

$ 
$