jujiro_eb has asked for the wisdom of the Perl Monks concerning the following question:

Hello Monks, I am relatively new to Perl but very comfortable with Korn Shell scripting. Can someone please help me with the following code: 
system ("sqlplus.exe -s uid/pwd@myserver "); <<EOT
select * from emp;
exit
EOT

[download]
Or using __DATA__ marker. I am sure you get the idea that I want to pass a bunch of SQL statments to sqlplus and capture the stdout (Which I know how to do). I do not want to resort to storing sql statements in a separate file and using something like this: 
system ("sqlplus.exe -s uid/pwd@myserver  @myscript.sql");

[download]
Any help will be highly appreciated.

Ash

Replies are listed 'Best First'.
Re: Question on inline redirection
by kyle (Abbot) on Mar 17, 2009 at 18:55 UTC

    First, you should know that double quotes interpolate, and Perl will try to interpolate an array named @myserver with code that says "sqlplus.exe -s uid/pwd@myserver". To avoid the interpolation, you can either put a backslash in front of the at sign to escape it, or use single quotes which don't interpolate ('sqlplus.exe -s uid/pwd@myserver' or "sqlplus.exe -s uid/pwd\@myserver"). If you Use strict and warnings, that will catch a mistake like that and lots of others.

    Second, it looks as if you're trying to pipe data to this program. In Perl, you'd do that with open like so: 

    open my $pipe_fh, '|-', 'sqlplus.exe -s uid/pwd@myserver'
    or die "Can't open pipe: $!";

    [download]

    Then you can print a heredoc into the pipe: 

    print {$pipe_fh} <<'END_OF_SQL'
select * from emp;
exit;
END_OF_SQL
;

    [download]

    Note that I put single quotes around the heredoc terminator to make it not interpolate. The default is interpolation, which you also get if you put the terminator in double quotes.

    If you're using __DATA__, that could look like this: 

    print {$pipe_fh} <DATA>;

    [download]

    When you close the pipe, Perl will wait for the program to finish, and you get its exit status in $?. 

    close $pipe_fh  or die "close failed: $!";
if ( $? ) {
    die "sqlplus returned non-zero exit status '$?'";
}

    [download]

    If you use English, you can refer to $? as $CHILD_ERROR instead, which is a bit more readable.

    Update: Here's all the code I suggested together in one block:

    Read more... (683 Bytes)
[reply]
[d/l]
[select]
      Kyle,

      You are my Monk of the year!

      Your explanation not only fixed my issue. It also gave me a quick lesson on Quoting. I honestly did not think that single and double quotes acted the same way in Perl as they do in Unix shell scripts.

      Thank you so much.

      Regards.

      Ash

[reply]
        Hi, The solution described above help me to solve the problem where I needed to fire sqlplus query from perl script. I was using .sql file method earlier as below and was searching where I can pass the query straight to Perl. $account = `sqlplus -s CPS_RW_USR/cps_rw_usr\@CPS_T3A.O2 \@test.sql`; However the problem now I am facing is with the above solution that I cant put any variables in the query as it gives error. For eg. I want to fire below query on emp table where id=10. So I did something like: my $id=10; __DATA__ select * from emp where id='$id'; exit; However it says no row selected. The problem is that value of variable is not getting detected in this script. Can you guys please help. Please reply me on veki.ldh@gmail.com Thanks Vivek Malhotra
[reply]
Re: Question on inline redirection
by JavaFan (Canon) on Apr 28, 2012 at 22:59 UTC
    Considering that you are familiar with the Korn shell, there's no reason to give up on that knowledge. Use nested here documents, one in Perl, the other in the shell (untested code): 
    my $output = <<`EOT1`;
sqlplus.exe -s uid/pwd\@myserver <<EOT
select * from emp;
exit
EOT
EOT1

... do something with $output ...

    [download]
[reply]
[d/l]

Back to Seekers of Perl Wisdom