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

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. 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 (andy@andyh.co.uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)