Beefy Boxes and Bandwidth Generously Provided by pair Networks
P is for Practical

Re^4: Get multiple rows in Perl from Oracle

by kalyanrajsista (Scribe)
on Dec 24, 2009 at 12:25 UTC ( #814228=note: print w/replies, xml ) Need Help??

in reply to Re^3: Get multiple rows in Perl from Oracle
in thread Get multiple rows in Perl from Oracle

Here is my code

use strict; use warnings; use DBIx::Connection; use DBIx::PLSQLHandler; my $Datasource = "dbi:Oracle:sid=dev;host="; my $connection = DBIx::Connection->new( name => 'dev', dsn => $Datasource, username => 'scott', password => 'tiger', ); my $plsql = $connection->plsql_handler( plsql => "DECLARE TYPE group_type IS RECORD ( h1 groups.HANDLE_GROUP%TYPE, g1 groups.GHANDLE%type); group_rec group_type; CURSOR data_groups IS SELECT DISTINCT ghandle FROM groups; BEGIN OPEN data_groups; FETCH data_groups INTO group_rec.g1; WHILE data_groups % FOUND LOOP SELECT handle_group INTO group_rec.h1 FROM groups WHERE ghandle = group_rec.g1 AND rownum < 2; DBMS_OUTPUT.PUT_LINE(group_rec.g1 || '==>' + || group_rec.h1); :h2 :=group_rec.h1; :g2 :=group_rec.g1; FETCH data_groups INTO group_rec.g1; END LOOP; CLOSE data_groups; END;" ); my $result_set = $plsql->execute(); print Dumper($result_set);

The above code is returning only one record, but when I execute my procedure in SQL Developer it is outputting many records

Any problem in my procedure...though

Replies are listed 'Best First'.
Re^5: Get multiple rows in Perl from Oracle
by Corion (Pope) on Dec 24, 2009 at 13:17 UTC

    Usually when working with DBI and things interacting with it, the result from ->execute() only indicates success or failure. I presume you're supposed to ->fetch (or ->fetchall_arrayref) the results.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://814228]
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others studying the Monastery: (13)
As of 2017-04-25 19:10 GMT
Find Nodes?
    Voting Booth?
    I'm a fool:

    Results (462 votes). Check out past polls.