Capturing SQL Output in Perl

Hello,

My question involves both Perl and SQL.

I'm using the perl code below to open a pipe to an Oracle database and pass
some transactions to it (contained in file queries.txt):

open(SQL, "| sqlplus -s scott/tiger\@database")or   die "Can't run SQL*Plus: $!\n";
open(QRY, "queries.txt") or die "Can't open queries.txt: $!\n";

$queries=<QRY>;
while ($queries ne "") {

   print SQL "$QRY\n"; # want to capture output here
$queries=<QRY>; # skip a line of non-query text
$queries=<QRY>;

}

My problem is that at the point where I print a line back to SQL (which contains a transaction), I'd like to capture the output from Oracle and take some action based on a success or non-success.

With the help of a Perl FAQ and reading Deja News archives, I'm able to

redirect output to a file, but only at the "open(SQL...)" line. That doesn't work because it captures the output ok, but it captures the output
for the entire SQL session -- but I need to be able to detect an error in
Oracle as soon as it happens, take some action, then move on and process

the next transaction.

Is there a way to catch the output coming back from Oracle and put it in

a Perl variable? If so, I could examine the variable as soon as each transaction is passed to Oracle and take whatever action I need to.

Is this something that must be done in Oraperl or with some of the SQL-friendly perl modules? I'd hate to have to re-write this script from
scratch (very long), so I'm hoping for a non-Oraperl way.

Thanks for your help,

Ryan Received on Wed Jul 08 1998 - 13:58:30 CDT