How to handel sqlplus error in perl script.

[Ankur_kuls]

Hi, I have a perl script which uses sqlplus for data processing. below is a part of my code...

##### Generating log file---
my $logfile = "${basePath}/VoucherStateChange.$filedate.log";
open (STDOUT, ">>", "$logfile") or die "Can't open logfile $logfile fo
+r writing: $!\n";
open (STDERR, ">>", "$logfile") or die "Can't open logfile $logfile fo
+r writing: $!\n";
print "$logfile\n";

my $SQLPLUS='/opt/oracle/product/11g/db_1/bin/sqlplus -S system/coolma
+n7@vsdb';
`$SQLPLUS \@${basePath}/VoucherQuery1.sql $startdate> ${basePath}/Quer
+yResult1.txt`;

Now output of my query which is written in VoucherQuery1.sql is being redirect to QueryResult1.txt. Now If I want that in case of any error, error message comes to the log file (oucherStateChange.$filedate.log) taken care by perl script instead of this QueryResult1.txt file. is it possible? thanks a lot...

[graff]

I would agree with the first reply, that it would be prudent to store stderr and stdout to separate output files.

Apart from that, you want to use a system call instead of backticks, because that way, any STDOUT content from the sub-shell will go to your script's STDOUT, and likewise for STDERR. Consider the following demonstration:

#!/usr/bin/env perl

use strict;
use warnings;

open( STDOUT, '>>', '/tmp/foo.log' );
open( STDERR, '>>', '/tmp/foo.err' );

my $testfile = "/tmp/test.$$.txt";

   # this should create a test file and produce no other output:
my $failure = system( "echo this is a test > $testfile" );

   # this adds a message to foo.err:
if ( $failure ) {
    warn "Failed to create/rewrite $testfile\n";
} else {
    warn "$testfile was successfully created/rewritten\n";
}

   # this adds a message to foo.log:
printf "size of $testfile is %d bytes\n", -s $testfile;

   # this adds the content of the test file to foo.log:
system( "cat $testfile" );

   # this adds an error message to foo.err:
system( "cat /tmp/no.such.file" );

   # the next two systems calls produce no output at all
   # (because the output is being redirected to /dev/null): 
system( "cat $testfile > /dev/null" );
system( "cat /tmp/no.such.file 2> /dev/null" );

   # the next system call will append data to $testfile,
   # AND append the same data to "foo.log":
system( "echo yet another test | tee -a $testfile" );

The point about the last three system calls is to show how you can control what does and doesn't reach your script's STDOUT and STDERR.

[Anonymous Monk]

I'd suggest you use a module to call the external command, not backticks. There are several modules that give you the ability to capture and redirect a command's STDOUT and STDERR: Capture::Tiny, IPC::Run3, and IPC::Run, the latter two even allow for you to feed custom STDIN to the command. Then you won't have to deal with the additional layer of the shell and you can do everything directly in Perl. Also, shell interpolation can introduce security holes.

Another thing, which is a matter of taste: I usually don't redirect my program's STDOUT and STDERR. If I want a logfile, I open it and write to it specifically (print $logfh "whatever\n";), and if my program produces any other output I let whoever is calling my Perl script deal with it (cron, a daemon runner, the user on the terminal, etc.).

[Ankur_kuls]

Thanks all for your inputs.. I have installed IPC::Run on my machine... I have googled but not able to find exactly how to run it. I am running below command

\

open (WFH1, ">", "${basePath}/QueryResult4.txt");
run('$SQLPLUS \@${basePath}/VoucherQuery4.sql $startdate', '>', \\WFH1
+);
close(WH1);

instead of the existing

 `$SQLPLUS \@${basePath}/VoucherQuery4.sql $startdate> ${basePath}/QueryResult4.txt`

but receiving error

 Global symbol "$WFH1" requires explicit package name at VoucherStateC
+hange.pl_other2 line 47.
Global symbol "$WFH1" requires explicit package name at VoucherStateCh
+ange.pl_other2 line 48.
Execution of VoucherStateChange.pl_other2 aborted due to compilation e
+rrors.

Feeling myself clueless...Could you please help me here or provide any good documentation on this module? thanks

[Anonymous Monk]

Write like this

open my($filehandle) ...
run ... $filehandle