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

I have used the DBI and have written select statements, but I cannot find a way to call stored procedures from PERL. 
$dbh = DBI->connect (dbname, $dbuser, $dbpass, 'Oracle');

if (!defined $dbh) {
    print LOG "Cannon connect to Oracle: $DBI::errstr\n";
    die "Cannont connect to Oracle: $DBI::errstr\n";
}
$sth1 = $dbh->prepare ("select user
                        from users
                        where state = 'iowa'");

[download]
I would like to replace the select statement with a oracle stored procedure call because I want all my SQL to be compiled on the DB.

Replies are listed 'Best First'.
Re: calling stored procedures in oracle
by btrott (Parson) on Mar 21, 2000 at 04:14 UTC
    There may be an easier/better way, but I think you can just do this: 
    my $rv = $dbh->do("begin foo_procedure(?); end;", undef, 'iowa')
    or die "Can't do: ", $dbh->errstr;

    [download]
    where 'iowa' is, obviously, just an example of a bind variable.
[reply]
[d/l]
      How do I get the output from the stored procedure the $rv is a boolean.
[reply]
        If the drivers for Oracle are like Informix you just have to treat the stored procedure just like a normal query: execute it first, then fetch from it to retreive the values. ie. my $sth = $dbh->prepare("execute new_procedure()"); $sth->execute(); my $rowref = $sth->fetch(); However a quick scan of my laptop shows that perldoc DBD states that calling stored procedures is driver dependent, so it may be slightly different for Oracle, good luck.
[reply]

Back to Seekers of Perl Wisdom