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
