Beefy Boxes and Bandwidth Generously Provided by pair Networks
Pathologically Eclectic Rubbish Lister

PL/SQL Functions.

by ant (Scribe)
on May 29, 2001 at 15:03 UTC ( #83878=perlquestion: print w/ replies, xml ) Need Help??
ant has asked for the wisdom of the Perl Monks concerning the following question:

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

Comment on PL/SQL Functions.
Re: PL/SQL Functions.
by jorg (Friar) on May 29, 2001 at 16:12 UTC
    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)


    "Do or do not, there is no try" -- Yoda
Re: PL/SQL Functions.
by busunsl (Vicar) on May 29, 2001 at 16:25 UTC
    bind_param_inout is meant for stored procedures, not for functions.
    For functions use a combination of bind_col and placeholders.
      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;


      "Do or do not, there is no try" -- Yoda
Re: PL/SQL Functions.
by arturo (Vicar) on May 29, 2001 at 16:29 UTC

    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"'

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://83878]
Approved by root
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others drinking their drinks and smoking their pipes about the Monastery: (6)
As of 2014-07-29 08:40 GMT
Find Nodes?
    Voting Booth?

    My favorite superfluous repetitious redundant duplicative phrase is:

    Results (211 votes), past polls