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

[bigjojoi]

Hi Dave sorry but I can't to do that. Can you tel me why ?
Thank you verry much,
George

<code pl/sql>

--**************************************************************************
****
--**************************************************************************
****
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

--
ix BINARY_INTEGER := 0;
debut pkg_sample.TABLE_OF_DATE;
desg pkg_sample.TABLE_OF_STRING80;

BEGIN

DBMS_OUTPUT.ENABLE(1000000);
DBMS_OUTPUT.PUT_LINE('-1->'||'time:'||to_char(sysdate,'hh24:mi:ss'));

FOR rec_val IN
(
SELECT
ename, empno
FROM
emp
ORDER BY
empno asc
)
LOOP
ix := ix + 1;
debut(ix) := rec_val.empno;
desg(ix) := rec_val.ename;
monTest(ix):= rec_val.ename;
END LOOP;

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;

$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);
end;
});
$sth1->execute;
$sth1 = $dbh->prepare(q{
BEGIN
:cursor := sp_ListEmp;
END;
});
my $sth2;
$sth1->bind_param_inout(":cursor", \$sth2, 0, { ora_type => ORA_RSET } );
$sth1->execute();
while ( my @row = $sth2->fetchrow_array ) { print join("|",@row),"\n"; }
<END code perl>



"Dave Hau" <davehau_nospam_123@nospam_netscape.net> a écrit dans le message
de news: 3F01D4C9.10507@nospam_netscape.net... Take a look at: http://asktom.oracle.com/~tkyte/ResultSets/index.html Cheers, Dave bigjojoi wrote: Hello, 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. Any help in this matter would be highly appreciated. Regards George