Compare a content of variable to a database column

Shell Programming and Scripting
1

Hi

have an array like this

colarray[0]="a"
colarray[1]="b"
colarray[2]="c"
colarray[3]="d"
colarray[4]="e"
colarray[5]="f"

the arrayvariable is in unix sh file

i want to check the content of the array to oracle database table.
that is whether "a" is present in the table.

2

unix variables are available inside the interactive program.
you just need to do the database processing to achieve whatever you want.

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

Oracle? mysql?
Do you want to know if a field in a table has the letter or any field?

4

its oracle database.
yes i want to check the table field contains the cahracter or may be a value.

thanks.

5

If you want to check whether the Oracle database column has any of the values of your shell array, then here's a method.

These are the contents of my Oracle table -

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>

Here's the Bash shell script:

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

And here's the script execution:

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

$ 
$

HTH,
tyler_durden