Beefy Boxes and Bandwidth Generously Provided by pair Networks
Syntactic Confectionery Delight
 
PerlMonks  

Re: Binding "Out" Parameters to Table Types for Oracle PL/SQL Stored Procedures

by enoch (Chaplain)
on Jun 06, 2003 at 19:07 UTC ( #263821=note: print w/ replies, xml ) Need Help??


in reply to Binding "Out" Parameters to Table Types for Oracle PL/SQL Stored Procedures

And the answer:

While one cannot pass back package-defined SQL variable types from Oracle stored procedures and functions, one can pass back cursors. And, you can define the cursor any way you see fit. So, here's how to accomplish the above.

First, you need to redefine the procedure to not return a "TABLE" type; rather, it should return a cursor (*note* I changed the procedure to a function because it seemed better that way now that it is just returning a var).

CREATE OR REPLACE PACKAGE foo AS TYPE deptCurs IS REF CURSOR; FUNCTION getAllDept RETURN foo.deptCurs; END foo;
Your package body, then simply becomes:
CREATE OR REPLACE PACKAGE BODY foo AS FUNCTION getDeptCurs RETURN foo.deptCurs IS crsr foo.deptCurs; BEGIN OPEN crsr FOR SELECT * FROM luDept; RETURN(crsr); END getDeptCurs; END foo;
To get at this from Perl, you use the following code:
my $dbh =DBI->connect('dbi:Oracle:host=hostname;sid=theSID', 'user', 'pass', { RaiseError => 1, AutoCommit => 0 }) || die "Database connection not made because:\n\t$DBI::errst +r\n"; my $deptRecords; eval { my $func = $dbh->prepare(q{ BEGIN :curs := foo.getDeptCurs(); END; }); # must specify the ora_type of ORA_RSET # so code knows it is a cursor $func->bind_param_inout(":curs ", \$deptRecords, 0, { ora_type => ORA_RSET}); $func->execute; $func->finish; }; if( $@ ) { warn "Execution of stored procedure failed because:\n\t$DBI::errstr +\n$@"; } while(my $hashRef = $deptRecords->fetchrow_hashref) { foreach(keys %$hashRef) { print "$_ is $hashRef->{$_}\n"; } } $deptRecords->finish; $dbh->disconnect;

I'll leave this here for posterity just in case anyone else ever stumbles upon this particular problem and encounters Warnock's Dilemma.

enoch


Comment on Re: Binding "Out" Parameters to Table Types for Oracle PL/SQL Stored Procedures
Select or Download Code
Re^2: Binding "Out" Parameters to Table Types for Oracle PL/SQL Stored Procedures
by Anonymous Monk on Jul 06, 2007 at 08:30 UTC
    Since i am calling a remote stored procedure in PERL through a DB link. One of the OUT parameter is a record of type table. Is there any way of fetching this in PERL.I can only return a cursor if i call a local procedure first and then write the data in a table which i don't want to do.
Re^2: Binding "Out" Parameters to Table Types for Oracle PL/SQL Stored Procedures
by nbartusi (Scribe) on Sep 19, 2008 at 14:02 UTC
    I've found that I also had to add this code to the perl script:

    use DBD::Oracle qw(:ora_types);

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://263821]
help
Chatterbox?
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: (8)
As of 2014-08-29 19:22 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The best computer themed movie is:











    Results (287 votes), past polls