Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl: the Markov chain saw
 
PerlMonks  

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

by enoch (Chaplain)
on Jun 05, 2003 at 20:01 UTC ( #263484=perlquestion: print w/ replies, xml ) Need Help??
enoch has asked for the wisdom of the Perl Monks concerning the following question:

I am having a problem getting Perl to bind OUT parameters correctly to Oracle stored procedures using DBD::Oracle. Everything works fine when I am binding them to normal PL/SQL variable types such as NUMBER, VARCHAR2, etc. The real gotcha comes when I try to bind parameters to data types defined in an Oracle package as:
TYPE myTable IS TABLE OF DeptTable%rowtype INDEX BY BINARY_INTEGER;
For example, say I create the ultra-simplistic package:
CREATE OR REPLACE PACKAGE foo AS PROCEDURE getDept(dept_id IN NUMBER, dept_name OUT VARCHAR2); END foo; CREATE OR REPLACE PACKAGE BODY foo AS PROCEDURE getDept(dept_id IN NUMBER, dept_name OUT VARCHAR2) IS BEGIN SELECT dept INTO getDept.dept_name FROM Dept WHERE idDept = getDept.dept_id; END getDept; END foo;
The Perl code to execute this and pull out the name of the department (dept_name) is simple and works just fine:
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::errs +tr\n"; my $deptName; eval { my $func = $dbh->prepare(q{ BEGIN foo.getDept ( :parameter1, :parameter2 ); END; }); $func->bind_param(":parameter1", 9); $func->bind_param_inout(":parameter2", \$deptName, 4); # bind this + parameter as an "inout" $func->execute; }; if( $@ ) { warn "Execution of stored procedure failed because:\n\t$DBI::errstr +\n$@"; } else { print "$deptName\n"; } $dbh->disconnect;
That code correctly prints out the department name. Now, comes the kicker, let's say I want to write a procedure that returns all of the departments. That is, it returns the whole department table. Something like:
CREATE OR REPLACE PACKAGE foo AS -- PROCEDURE getDept(dept_id IN NUMBER, dept_name OUT VARCHAR2); /* This is a package wide variable type that can hold the contents of the entire department table */ TYPE deptTable IS TABLE OF dept%rowtype INDEX BY BINARY_INTEGER; PROCEDURE getAllDept(dept OUT deptTable); END foo; CREATE OR REPLACE PACKAGE BODY foo AS PROCEDURE getAllDept(dept OUT deptTable) IS CURSOR cursDept IS SELECT * FROM dept; i NUMBER := 0; BEGIN OPEN cursDept; LOOP FETCH cursDept INTO getAllDept.dept(i); /* put all of the +dept table's records in the variable */ i := i + 1; EXIT WHEN cursDept%NOTFOUND; END LOOP; CLOSE cursDept; END getAllDept; END foo;
It's a fairly simple procedure and works just fine. Now, I write the Perl code to execute it and gather the results.
use Data::Dumper; 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::errs +tr\n"; my $deptRecords; eval { my $func = $dbh->prepare(q{ BEGIN foo.getAllDept ( :parameter1 ); END; }); $func->bind_param_inout(":parameter1", \$deptRecords, 4096); $func->execute; }; if( $@ ) { warn "Execution of stored procedure failed because:\n\t$DBI::errstr +\n$@"; } else { print Dumper($deptName); } $dbh->disconnect;
This returns the error that I am not using the right data type.
Execution of stored procedure failed because: ORA-06550: line 3, column 4: PLS-00306: wrong number or types of arguments in call to 'GETALLDEPT' ORA-06550: line 3, column 4: PL/SQL: Statement ignored (DBD: oexec error)
So, my question is, how (assuming there is a way) can you bind Perl scalars to Oracle PL/SQL procedure OUT variables that are of a package-defined type? Can you? I have read over DBD::Oracle's POD examples, and I have read all the other pages I could find (Perl DBI Examples, DBI DBD::Oracle and OraPerl FAQ, etc.); and I can't find anything. Is there any Perl/Oracle-guru lurking around the Monastery?

Thanks,
enoch

Comment on Binding "Out" Parameters to Table Types for Oracle PL/SQL Stored Procedures
Select or Download Code
Re: Binding "Out" Parameters to Table Types for Oracle PL/SQL Stored Procedures
by enoch (Chaplain) on Jun 06, 2003 at 19:07 UTC

    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
      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.
      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: perlquestion [id://263484]
Approved by arthas
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others browsing the Monastery: (10)
As of 2014-09-03 02:32 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    My favorite cookbook is:










    Results (35 votes), past polls