How to use IPC::Run module in perl script

Question

I have this Perl script which connects to sqlplus database and run the below command.

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

Now I am trying to run the second command through IPC::Run. I have tried in many ways without any success. like

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

but it is not able to connect to sqlplus or write to the output file. I have searched in google and in other forums but haven't got any solution. Please help. :)

Answer 1

Using lexical filehandles makes life much easier in general. They aren't global, and they will automatically close when they go out of scope.

open (my $wfh1, ">", "${basePath}/QueryResult4.txt");

It's possible the whole problem is that open failed, you're not checking if it succeeded. You can do this two ways. First is to do it by hand...

my $query_result_file = "${basePath}/QueryResult4.txt";
open (my $wfh1, ">", $query_result_file)
    or die "Couldn't open $query_result_file for writing: $!";

Or you can use autodie to do it for you.

use autodie;
open (my $wfh1, ">", "${basePath}/QueryResult4.txt");

But you don't need to manually open the file at all, IPC::Run will do that for you if you pass it a filename.

The next problem is what you're passing to run. It's in single quotes, which means none of the variables will be interpolated. You're literally trying to run $SQLPLUS @${basePath}/VoucherQuery4.sql $startdate. You need double quotes.

But it's better to pass your command and arguments as an array ref, that way IPC::Run will handle the shell quoting for you.

Here's a simple example reading a file using cat, adding line numbers, and outputting the result to a file.

use IPC::Run qw(run);
run ["cat", "-n"], "<", "/etc/passwd", ">", "test.out";

Putting it all together...

my $SQLPLUS_EXE = '/opt/oracle/product/11g/db_1/bin/sqlplus';
my $SQLPLUS_DB  = 'system/coolman7@vsdb';
my @SQLPLUS_CMD = ($SQLPLUS_EXE, '-S', $SQLPLUS_DB);

run [@SQLPLUS_CMD, "\@${basePath}/VoucherQuery4.sql", $startdate],
    ">", "${basePath}/QueryResult4.txt";