Oracle SQLPlus / Unix Shell Script interaction

Sometimes you need interaction between between sqlplus and unix shell scripts, like passing some shell variables to sqlplus or executing shell scripts based on the output of an SQL-Select statement.

I used the following demo table:

[frank@w0004 ~]$ sqlplus /nolog
SQL> connect foo/bar
SQL> create table foobar (line varchar2(20));
SQL> insert into foobar values('test1');
SQL> insert into foobar values('test2');
SQL> insert into foobar values('test3');
SQL> commit;

And here comes the shell script I used:

#!/bin/bash
username=foo
password=bar

sqlplus -s /nolog << EOF | grep -v 'Connected' > tmpout.txt
set pagesize 0 feedback off verify off head off echo off
connect $username/$password
whenever sqlerror exit 1
select line from foobar;
exit;
EOF

if [ -f tmpout2 ]; then 
  rm tmpout2.txt 
fi

for i in `cat tmpout.txt`; do
sqlplus -s /nolog << EOF | grep -v 'Connected' >> tmpout2.txt
set pagesize 0 feedback off verify off head off echo off
connect $username/$password
whenever sqlerror exit 1
select * from foobar where line = '$i';
exit;
EOF
echo $i "row";
done