SQL*Plus inside Perl script

Question

I am having a prior knowledge of shell scripting. I am trying to connect to a table using SQL*Plus and fetch the data in a Perl script and store that output in a Perl variable. Normally in a shell script I would do like below:

    SQL_RESULT=`sqlplus -s ${CONNECT_STRING} << EOF
    ${SQLPLUS_SETTINGS}
    select foo||'|'||bar ||'|'|| xyz from temp where dfg='some';
    exit;
    EOF`

But how do I do this in Perl?

Answer 1

Check out the DBI module. In fact, there's a whole website dedicated to it: dbi.perl.org. Also, check out the CPAN module reference for DBI.

Here's a code example, straight from the first DBI tutorial on google:

    use DBI;

    my $dbh = DBI->connect('DBI:Oracle:payroll')
        or die "Couldn't connect to database: " . DBI->errstr;
    my $sth = $dbh->prepare('SELECT * FROM people WHERE lastname = ?')
        or die "Couldn't prepare statement: " . $dbh->errstr;

    $sth->execute($lastname)             # Execute the query
        or die "Couldn't execute statement: " . $sth->errstr;

    # Read the matching records and print them out          
    while (@data = $sth->fetchrow_array()) {
        my $firstname = $data[1];
        my $id = $data[2];
        print "\t$id: $firstname $lastname\n";
    }
    if ($sth->rows == 0) {
      print "No names matched `$lastname'.\n\n";
    }
    $sth->finish;
    print "\n";
    print "Enter name> ";

    $dbh->disconnect;

Perl also has that EOF style multiline comment; you can make a long query like this:

my $query = <<'END_QUERY';
${SQLPLUS_SETTINGS}
select foo||'|'||bar ||'|'|| xyz from temp where dfg='some';
exit;
END_QUERY

Answer 2

The advice to use the DBI is good, and definitely the right way to do things, if you're wanting to program Perl scripts against databases.

However, to answer your exact question, if you specifically want to script SQL*Plus, the syntax to do this with a Perl script is fairly similar to the shell version

my $connect_string = 'scott/tiger@test';
my $sqlplus_settings = '';
my $result = qx { sqlplus $connect_string <<EOF
$sqlplus_settings
select 1 from dual;
exit;
EOF
};
print $result;

The qx operator I use there, is just a politer form of backtick, everything within the brace delimited block is run by a subshell, and the output returned to the assignment. Variables aren't usually upper cased in Perl.

Answer 3

A couple of things:

• DBI is definitely the best way to go. However please note a previous answer I gave to an Oracle question which maybe "still" relevant: How can I use a database server from a Perl CGI script?

• SQL*Plus throws an error if your SQL was too long. It has a fixed line length buffer (I can't recall what it was but I think it was below 2000 chars with Oracle 8). There maybe a workaround (splitting lines up? configuration settings?) but I found switching to DBI the best solution for this and other reasons.

Caveat: All my information above is based on Oracle 8.

/I3az/

Answer 4

Any question of this type should be prefaced with "I can't use DBI because..." Because you really want to use DBI if at all possible. You might have good reason(s) not to use it, but maybe, we can tell you why your reasons aren't very good and what to do about it. That being said, here's one way to do what you asked, using fork and filehandles, and getting output one line at a time (warning: if you print too much to a process like this, it may block due to buffer issues):

use strict;
use warnings;

pipe(my($p_rdr, $c_wtr)) or die "Err: $!";
pipe(my($c_rdr, $p_wtr)) or die "Err: $!";
my $pid = fork;
die "Could not fork: $!" unless defined $pid;
unless ($pid) {
  close $p_rdr;
  close $p_wtr;
  open(STDOUT, ">&=", $c_wtr) or die "dup: $!";
  open(STDIN, "<&=", $c_rdr) or die "dup: $!";
  print "Exec sqlplus\n";
  exec qw(sqlplus user/passwd@dbname);
  die "Could not exec: $!";
}
close $c_wtr;
close $c_rdr;
print "Print sql\n";
print $p_wtr "select * from table_name where col1 = 'something';\n";
print "Close fh\n";
close $p_wtr;

print "Read results\n";
while (<$p_rdr>) {
  print "O: $_";
}
close $p_rdr;