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;
[download]

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;
[download]



Jorg

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

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);
[download]

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"'
[download]