in reply to Calling a plsql file from perl script

To add to toolic's comment, there a number of ways to invoke external programs from Perl. For some grotesque detail, see perlipc. The quick answer is that if you don't care about the output (STDOUT and STDERR will pipe to Perl's STDOUT and STDERR) and just need to execute a single command, system does what you need: 
system('sqlplus', $Username, "$Password\@SERVER", 'test.sql');

[download]
The return value will be the exit code of the process.

If you want to capture the STDOUT channel but are okay with STDERR bypassing Perl, you'll want backticks. 

my $result = `sqlplus $Username $Password\@SERVER test.sql`;

[download]
or if you want to capture STDOUT and STDERR 
my $result = `sqlplus $Username $Password\@SERVER test.sql 2>&1`;

[download]
Note that escaping can get hairy here.

Lastly, if you want to do anything more complex (bidirectional communication, handling STDOUT and STDERR separately, ...) it can be done, but you'll need to be specific.

#11929 First ask yourself `How would I do this without a computer?' Then have the computer do it the same way.

Replies are listed 'Best First'.
Re^2: Calling a plsql file from perl script
by chai6125 (Initiate) on Sep 03, 2014 at 19:59 UTC
    hi kennethk is it possible to call a sql as you have mentioned directly using sqlplus ? I have heard some people saying to use dbi i really didnt understand that can you please explain me
[reply]
      Perl's DBI interface is excellent; however, it is intended for SQL not PL/SQL. If you do need procedures run, the traditional method is to use stored procedures invoked via standard queries (SELECT, INSERT...). If I was going to implement this, I would do the logic and looping in Perl, and the database interaction via DBI. Something like (untested): 
      use strict;
use warnings;
use DBI;

my $db = DBI->connect(  $dbid,
                        $user,
                        $password,
                        {
                            PrintError  => 0,
                            RaiseError  => 1,
                            AutoCommit  => 0,
                        },
                    );
my $query = $db->prepare(<<EOSQL);
INSERT INTO temp (field1, field2, field3)
VALUES(?, ?, ?)
EOSQL

my $x = 100;
for my $i (1 .. 10) {
    $query->execute($i, $x, $i % 2 ? 'i is odd' : 'i is even');
    $x += 100;
}
$db->commit;

      [download]
      Note that I've used a single query with placeholders rather than interpolation of values. This is both more efficient and protects you from SQL-injection and escaping issues. I've also taken advantage of the fact that 1 is true in Perl, thus bypassing an equality check. Finally, I explicitly included my target fields in the INSERT, since this makes you resilient to schema changes and makes maintenance more obvious.

      #11929 First ask yourself `How would I do this without a computer?' Then have the computer do it the same way.

[reply]
[d/l]

      Howdy chai6125, welcome to the Monastery! If you'll allow me to chime in:

      hi kennethk is it possible to call a sql as you have mentioned directly using sqlplus ?

      Yes, it is. Indeed, as kennethk pointed out, there's a variety of different ways you can do this, depending on your specific needs.

      I have heard some people saying to use dbi i really didnt understand that can you please explain me

      DBI is Perl's major database interface (for SQL databases, anyway). You could probably use it instead of calling an external command, but depending on the context in which you want to accomplish this, I'm not really seeing an upside to this: there's nothing wrong with invoking external tools, so long as they're available.

      Things might be different if you need to distribute your Perl script to machines that don't have sqlplus available, but until then I'd say don't overengineer things.

      HTH!

[reply]

In Section Seekers of Perl Wisdom