Beefy Boxes and Bandwidth Generously Provided by pair Networks
Think about Loose Coupling

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

Replies are listed 'Best First'.
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 all is quiet...

How do I use this? | Other CB clients
Other Users?
Others having an uproarious good time at the Monastery: (5)
As of 2017-02-25 19:38 GMT
Find Nodes?
    Voting Booth?
    Before electricity was invented, what was the Electric Eel called?

    Results (368 votes). Check out past polls.