Beefy Boxes and Bandwidth Generously Provided by pair Networks
Don't ask to ask, just ask

Re^2: Get multiple rows in Perl from Oracle

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

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

I agree that DBI does lots of things, but I'm trying to execute a stored procedure which returns data rows. I'm successfully executing procedure and is returning only the last row from DB output. How can i get all the rows I'm trying the following modules....
use DBIx::Connection; use DBIx::PLSQLHandler;

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

    The documentation seems fairly explicit. You could show us some code, but that would spoil the fun of extracting the relevant information piece by piece from you.

    So, my next question is, is it a mineral?

      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

        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://814221]
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others rifling through the Monastery: (4)
As of 2016-09-01 03:45 GMT
Find Nodes?
    Voting Booth?
    The best thing I ever won in a lottery was:

    Results (439 votes). Check out past polls.