Returning tables from PL/SQL function to DBI-perl script
bigjojoi
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
Andy Hassall
>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. The
following works on Oracle 9.2:
create or replace type floatTab is table of number;
create or replace package tabTest
as
function floatTabFunc
return floatTab;
end tabTest;
/
create or replace package body tabTest
as
function floatTabFunc
return floatTab
is
v_tab floatTab := floatTab(1, 2, 3, 4, 5);
begin
return v_tab;
end floatTabFunc;
end tabTest;
/
Then in Perl:
#!perl
use strict;
use warnings;
use DBI;
my $dbh = DBI->connect('dbi:Oracle:dev92',
'test', 'test',
{
RaiseError => 1,
AutoCommit => 0,
}) or die DBI->errstr;
my $sth = $dbh->prepare('select * from table(tabTest.floatTabFunc)
');
my $array = $dbh->selectcol_arrayref($sth);
print join ',', @$array;
print "\n";
$dbh->disconnect;
This outputs:
$ perl table.pl
1,2,3,4,5
--
Andy Hassall (an...@andyh.co.uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)
bigjojoi
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>
"Andy Hassall" <an...@andyh.co.uk> a écrit dans le message de news:
gk24gv0pc4qbd9lec...@4ax.com...
Andy Hassall
[post reformatted - please don't top-post]
>"Andy Hassall" <an...@andyh.co.uk> a écrit dans le message de news:
>gk24gv0pc4qbd9lec...@4ax.com...
>> On Tue, 1 Jul 2003 09:29:16 +0200, "bigjojoi" <bigj...@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.
bigjojoi
I would like only to say ... THANK You very much...
That go to help me so much
George
"Andy Hassall" <an...@andyh.co.uk> a écrit dans le message de news:
krf9gvc0n25nbhbgg...@4ax.com...