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

FC6, iSCSI + Oracle RAC and 23C3 -- new stuff for the cold days

Wuff, the year 2006 nearly finished with the speed of light. But there is still one month left with a lot of new stuff to check out.

Last week I upgraded my notebook from Fedora Core 5 to Fedora Core 6. Hey! Everything worked without a problem. Just the usual update of the fedora-release-6-4.noarch.rpm package followed by yum upgrade and I am on FC6...

There are two new OTN articles about iSCSI and Oracle RAC. The first one "Setting Up an Inexpensive iSCSI Linux Cluster Using SLES10 and OCFS2" is from Mark Fasheh and Todd Trichler. The Paper is quite short and only describes how to get the storage part working. This article is also included in the "How-To's" section on the "Download OTN's Greatest Hits CD". The second article "Build Your Own Oracle RAC 10g Release 2 Cluster on Linux and iSCSI" from Jeffrey Hunter includes an complete work through from OS installation to the RAC setup.

Finally the "Fahrplan"/Agenda of the 23C3 conference in Berlin got released too. That is definitely something you should check out...

Oracle Secure Enterprise Search 10g

Oracle is doing a lot of press releases at the moment. First they bought Sleepycat (Berkeley DB) and now they try to attack Google.com? I watched the demo of Oracle Secure Enterprise Search and I must admit the web interface looks very familiar to me :-).

I am wondering if that new product is somewhat related to YAPA the Oracle Text demo from OTN. May be YAPA is the predecessor of SES? A crawler/spider component could also be found as an demo on OTN called Indagine.

On the other hand, it is more likely that SES is the continuation of TripleHop MatchPoint which Oracle bought in June 2005.

Links: