Re: Returning tables from PL/SQL function to DBI-perl script

On Thu, 3 Jul 2003 16:16:08 +0200, "bigjojoi" <bigjojoi_at_hottot.com> wrote:

[post reformatted - please don't top-post]

>"Andy Hassall" <andy_at_andyh.co.uk> a écrit dans le message de news:
>gk24gv0pc4qbd9lecql64biogbptptea4t_at_4ax.com...
>> On Tue, 1 Jul 2003 09:29:16 +0200, "bigjojoi" <bigjojoi_at_hottot.com> wrote:
>>
>>>My problem is that I've a PL/SQL procedure, returning a table of float.
>>>I'd like to map the Oracle "TABLE OF FLOAT" to a perl array.
>>
>> DBD::Oracle (the DBI driver for Oracle) doesn't support PL/SQL table
>>types.
>>
>> But you can use the TABLE() operator to turn it into a result set.
>>

[snip PL/SQL]
>> function floatTabFunc
>> return floatTab;
>>

[snip Perl]
>> my $sth = $dbh->prepare('select * from table(tabTest.floatTabFunc)
>> ');
>
>Hi Andy sorry but I can't to do that. Can you tel me why ?

 You haven't posted any error messages, or any indication of what happens, and how it differs from what you expected, or your Oracle version (since TABLE() is relatively new?).

 But you've also missed the key point - DBD::Oracle does not support the PL/SQL table type, so you cannot expect it to be able to handle a PL/SQL table; you must convert it to a result set with TABLE().

> CREATE OR REPLACE PACKAGE pkg_sample AS
>TYPE TABLE_OF_DATE IS TABLE OF VARCHAR2(21) INDEX BY BINARY_INTEGER;
>TYPE TABLE_OF_STRING80 IS TABLE OF VARCHAR2(82) INDEX BY BINARY_INTEGER;
>
> PROCEDURE hello_1 (monTest IN OUT pkg_sample.TABLE_OF_STRING80 );
>
>END pkg_sample;
>/
>
> CREATE OR REPLACE PACKAGE body pkg_sample AS
>PROCEDURE hello_1 (monTest IN OUT pkg_sample.TABLE_OF_STRING80) IS
[snip cursor that populates monTest]
>END;
>END pkg_sample;
>/
>
><END code pl/sql>
>
><code perl>
>use strict;
>use DBI;
>use DBD::Oracle qw(:ora_types);
>
>my $dbh = DBI->connect('dbi:Oracle:','scott','tiger') or die $DBI::errstr;
>my $sth1 = $dbh->prepare(q{create or replace package types as
> type cursorType is ref cursor;
>end;});
>$sth1->execute;

 Better to create that up front from SQL*Plus, or next best do that with $dbh->do('create ...'), rather than a cursor+execute, but anyway...

>$sth1 = $dbh->prepare(q{
>create or replace function sp_ListEmp return types.cursorType
>as l_cursor pkg_sample.TABLE_OF_STRING80;
>begin
> pkg_sample.hello_1 (l_cursor);

 Complete mismatch of types here...

 l_cursor is a ref_cursor, yet you pass it to a function that takes an IN OUT parameter of a PL/SQL table type.

>end;
>});
>$sth1->execute;
>$sth1 = $dbh->prepare(q{
>BEGIN
> :cursor := sp_ListEmp;

 Not going to work, the types don't match.

 The procedure you're making above needs to be something vaguely like (untested):

create or replace function sp_ListEmp return types.cursorType as

   v_cursor types.cursorType;
   v_table pkg_sample.TABLE_OF_STRING80; begin

   pkg_sample.hello1(v_table);
   open v_cursor for select * from table(v_table);    return v_cursor;
end;

 The key being that you get a ref cursor back (which DBD::Oracle supports, using the ORA_RSET type that you've used). NOT a PL/SQL table.

--
Andy Hassall (andy@andyh.co.uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)