Rohan,
I do have a patch for DBD::Oracle 1.12 that allows for passing PL/SQL
Tables ( OCI Collection Types ) to/from PL/SQL. However, for the project
I initially developed the patch for, it turned out to be easier to use
straight C/OCI, so I must say the patch hasn't fully tested or used in a
production environment. If you'd like to give it a whirl, I'd be happy
to send you the patch and work through any issues with you.
However, all that being said, you may want to work around this scenario
by doing 1 of 2 things.
1) Wrap the PL/SQL with a package to use as an iterator. BY that, I mean
your wrapper package would call your third party stored procedure and
provide
a programatic interface into the PL/SQL table elements. The problem is, if
a
large number of items are returned from the PL/SQL table, it would mean a
database round-trip per call and may be too expensive.
2) use a CAST operator to convert it to a REF Cursor. This method works
pretty well, but the catch is I've run into problems when the PL/SQL table
type
is local to a package. In my experience, it needs to be global for this
method
to work. I like this method, since DBD::Oracle supports REF cursors well.
Here is example ( sorry, no DBD::Oracle code, just the PL/SQL ) of what I
mean
in #2. As Tim mentioned, the Collection support really belongs in the
Oracle::OCI,
but I haven't had a chance to take a look at it yet.
Hope this helps.
--
-- Quick example showing how to cast a PL/SQL table to a REF Cursor
--
set serveroutput on;
create or replace type str_v as table of varchar2(100);
/
CREATE OR REPLACE PACKAGE dbd_tst1 AS
TYPE ref_cur IS REF CURSOR;
PROCEDURE convert_to_ref(o_cur OUT ref_cur);
PROCEDURE return_plsql_table(o_plsql_table OUT str_v);
end dbd_tst1;
/
CREATE OR REPLACE PACKAGE BODY dbd_tst1 AS
PROCEDURE convert_to_ref(o_cur OUT ref_cur) IS
m_tbl str_v;
BEGIN
return_plsql_table(m_tbl);
OPEN o_cur FOR
SELECT * FROM TABLE(CAST(m_tbl as str_v));
NULL;
END;
PROCEDURE return_plsql_table(o_plsql_table OUT str_v) IS
BEGIN
o_plsql_table := str_v();
o_plsql_table.extend;o_plsql_table(o_plsql_table.count) := 'val1';
o_plsql_table.extend;o_plsql_table(o_plsql_table.count) := 'val2';
o_plsql_table.extend;o_plsql_table(o_plsql_table.count) := 'val3';
o_plsql_table.extend;o_plsql_table(o_plsql_table.count) := 'val4';
o_plsql_table.extend;o_plsql_table(o_plsql_table.count) := 'val5';
END;
END dbd_tst1;
/
DECLARE
o_cur dbd_tst1.ref_cur;
m_str varchar2(100);
BEGIN
dbd_tst1.convert_to_ref(o_cur);
LOOP
FETCH o_cur INTO m_str;
dbms_output.put_line('Fetched: ' || m_str);
EXIT WHEN o_cur%NOTFOUND;
END LOOP;
end;
/
> -----Original Message-----
> From: Tim Bunce [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, February 06, 2002 2:43 PM
> To: Rohan Holt
> Cc: '[EMAIL PROTECTED]'; Andy Sautins
> Subject: Re: passing out PL/SQL Tables from stored procs with DBI
> (DBD::Oracle )?
>
>
> On Wed, Feb 06, 2002 at 12:26:23PM -0000, Rohan Holt wrote:
> > I would like to use DBI & DBD::Oracle to call a third party
> (proprietry)
> > stored proc API which happens to use PL/SQL tables as out
> parameters in some
> > cases.
> >
> > Problem:
> > Having scanned many of the questions & answers relating to
> stored procs in
> > this list it seems that this is not possible. The closest I
> came to a
> > solution is binding to an open cursor. As I have no control
> over the coding
> > of the stored proc this is not really an option.
>
> You can write a PL/SQL wrapper.
>
> > Has anyone been able to return data from PL/SQL tables
> using DBI with
> > Oracle? Is there likely to be any support for this soon (if
> there isn't
> > already)?
>
> Not unless someone (ie a company) sponsors the work to raise
> it's priority
> (I'll get to it eventually but I've a pile of other DBI, DBD::Oracle,
> Oracle::OCI, and extproc_perl work to do first).
>
> Having said that, Andy Sautins <[EMAIL PROTECTED]> (CC'd)
> has a patch
> for DBD::Oracle that implements collections. Something along
> those line
> will get into DBD::Oracle eventually (probably in conjunction
> with Oracle::OCI).
>
> Tim.
>
