PL/SQL Functions.

[ant]

Hi Folks
I have been using the Perl DBI to access and return data
from an Oracle database using PL/SQL packages and procedures.

By using param_inout (e.g. bind_param_inout(6,\$result,252);)
I can return the data from the database.
But how do I return data from a PL/SQL function? If the function
takes in 4 parameters and returns a number, do I use 5 binded
variables and the 5th variable is the returned number (does
not seem to work)
or is there another way?? Thanks for any help in advance
Ant

[jorg]

A while back when i was doing similar stuff i found a few perl-dbi-dbd:oracle code snippets here that were very usefull to me. I believe the one you're after is similar to this :


use strict;
use DBI; my $dbh = DBI->connect(
    'dbi:Oracle:orcl',
    'jeffrey',
    'jeffspassword',
    {
        RaiseError => 1,
        AutoCommit => 0
    }
) || die "Database connection not made: $DBI::errstr"; my $rv; #holds 
+the return value from Oracle stored procedure
eval {
    my $func = $dbh->prepare(q{
        BEGIN
            :rv := jwb_function(
                parameter1_in => :parameter1
            );
        END;
    });
 
    $func->bind_param(":parameter1", 'Bunce');
    $func->bind_param_inout(":rv", \$rv, 6);
    $func->execute;
 
    $dbh->commit;
}; if( $@ ) {
    warn "Execution of stored procedure failed: $DBI::errstr\n";
    $dbh->rollback;
} print "Execution of stored procedure returned $rv\n"; $dbh->disconne
+ct;

The binding of out vars happens here : $func->bind_param_inout(":rv", \$rv, 6);. The DBI assigns the return value to a reference ($rv). If i remember correctly, '6' is the number of bytes to use or the datatype (not sure on this, check the docs)

Jorg

"Do or do not, there is no try" -- Yoda

[busunsl]

bind_param_inout is meant for stored procedures, not for functions.
For functions use a combination of bind_col and placeholders.

[jorg]

The DBD::Oracle documentation specifies that one can (not saying should) use bind_param_inout for stored functions.

Snippet from DBD::Oracle docs (comes installed with DBD::Oracle, search for DBD/Oracle.html in your Perl source tree)

# Example 4
#
# What about the return value of a PLSQL function?
# Well treat it the same as you would a call to a function 
# from SQL*Plus. We add a placeholder for the return value 
# and bind it with a call to bind_param_inout so
# we can access it's value after execute.

  my $whoami = "";
  $csr = $db->prepare(q{
        BEGIN
            :whoami := PLSQL_EXAMPLE.FUNC_NP;
        END;
  });
  $csr->bind_param_inout(":whoami", \$whoami, 20);
  $csr->execute;
  print "Your database user name is $whoami\n";
  $db->disconnect;



Jorg

"Do or do not, there is no try" -- Yoda

[arturo]

Untested idea: when you want the value of a function all by itself (you're not using it as part of a larger query), SELECT the value of the function from the special table DUAL and use the ol' INTO keyword:

my $sth = $db->prepare(q{
    SELECT my_function(:p1, :p2, :p3, :p4) INTO :p5 FROM dual
          });
# bind other params
$sth->bind_param_inout(":p5", \$return_value, 252);

HTGYSI (hope this gives you some ideas =)

perl -e 'print "How sweet does a rose smell? "; chomp ($n = <STDIN>); 
+$rose = "smells sweet to degree $n"; *other_name = *rose; print "$oth
+er_name\n"'