Having played with this a bit just now, it looks like a file handle that is writing to a pipe will stay open until the system generates a SIGPIPE, at which point the next attempt to print to that handle will return false. The problem is that you end up doing at least one print after the pipe process is actually dead, before there's a SIGPIPE. Consider the following script (taking the place of sqlplus in your app):

#!/usr/bin/perl
while (<>)
{
    die "Pipe process is now dead\n" if ( /kill/ );
    print "Pipe process got $_";
    exit(0) if ( $. == 5 );
}
[download]

That will exit with success after it prints the five lines from STDIN, unless one of those lines contains the word "kill", in which case it dies with an error.

Now consider this routine for feeding that script from a loop; there are a couple variants on this theme, which you can control with the "config" variables "$useSig,$useKill,$useSleep":

#!/usr/bin/perl
use strict;

my ($useSig,$useKill,$useSleep) = (1,0,1);
my $deadPipe = 0;
$SIG{PIPE} = \&catchSIGPIPE if $useSig;

$|++;  # Don't buffer stdout
open( TOPIPE, "| /tmp/test.perl" ) || die "Pipe process would not star
+t\n";
select TOPIPE;
$|++;  # Don't buffer TOPIPE

my @test = qw/zero one two three four five six/;
$test[2] = "kill" if $useKill;

print STDOUT "Config: useSig=$useSig, useSleep=$useSleep, useKill=$use
+Kill\n";
for (@test) {
   last if ( $useSig && $deadPipe );
   print STDOUT "sending $_ to pipe process\n";
   print TOPIPE "$_\n" or last;
   sleep 2 if $useSleep;
}

sub catchSIGPIPE
{
   $deadPipe++;
}
[download]

The results are interesting; obviously, if you allow output buffering on the pipe handle, it will take longer to get a SIGPIPE interrupt, but even when you turn off buffering, the timing of the "for" loop can determine whether the signal will be caught -- when I turned off the "$useSleep" on my Pentium/linux, all seven iterations of "sending ..." were printed first, then the 3 or 5 reports from the pipe process, depending on whether "$useKill" was on or off.

2:Can I achive my desired results by saying print HANDLE "statement" or last;?

The short answer is "no". You'll always write at least one statement to the handle after the handle's process has ended; if the handle is buffered and/or the loop iterates quickly, you'll write many statements before realizing that the handle's process has ended.

So my question is: are you really not able to look at one of these statements, before you write it to the pipe handle, and figure out whether it will cause sqlplus to exit? If exits might be caused by erroneous statements, then what other sorts of mayhem might be inflicted on table data without causing sqlplus to exit? ("Ooops, that last list of statements had "delete" instead of "update"...)

Another question might be: why are you writing statements to sqlplus through a pipe handle, rather than using DBI and DBD::Oracle? Granted, if you don't have these installed and/or haven't used them yet, it's a steep learning curve at first, but a short one, usually; and it's also true that these are slower than "native" oracle apps like sqlplus and sqlloader. Still, the overall result would be more robust than losing track of which statements were executed and which were written to a dead pipe...