Re: table output from stored procedure

From: Bruce Szalwinski <bruceandsusan_at_austin.rr.com>
Date: Tue, 15 Aug 2000 02:00:34 GMT
Message-ID: <6v1m5.53664$0l6.125033@typhoon.austin.rr.com>

You want your stored procedure to have an OUT parameter that is a cursor. Here is a blurb from the DBD-Oracle reference:

Binding Cursors
Cursors can now be returned from PL/SQL blocks. Either from stored procedure OUT parameters or from direct OPEN statements, as show below:

  use DBI;
  use DBD::Oracle qw(:ora_types);
  $dbh = DBI->connect(...);
  $sth1 = $dbh->prepare(q{

      BEGIN OPEN :cursor FOR
          SELECT table_name, tablespace_name
          FROM user_tables WHERE tablespace_name = :space
      END;

  });
  $sth1->bind_param(":space", "USERS");
  my $sth2;
  $sth1->bind_param_inout(":cursor", \$sth2, 0, { ora_type => ORA_RSET } );   $sth1->execute();
  # $sth2 is now a valid DBI statement handle for the cursor   while ( @row = $sth2->fetchrow_array ) { ... }

The only special requirement is the use of bind_param_inout() with an attribute hash parameter that specifies ora_type as ORA_RSET. If you don't do that you'll get an error from the execute() like: ``ORA-06550: line X, column Y: PLS-00306: wrong number or types of arguments in call to ...''.

---

Hope that helps.

    -bruce
"Aymeric Duclert" <aduclert_at_freesurf.fr> wrote in message news:39986E0A.9620EC41_at_freesurf.fr...
> Hi,
>
> I'm used to Sybase but new to Oracle. I'd like to build stored
> procedures that output tabular informations (such as the result of table
> selects). But since a PL/SQL stored procedure cannot do a 'select'
> without an 'into', how can a client get a table result from a stored
> procedure ?
>
> I would like to do something like the one below (in more complicate of
> course : a view is not enough !), but it does not work :
> create procedure myProc(x number) is
> begin
> select * from myTable where myCol < x;
> end;
>
> I'm working on Oracle8i under linux and my client is for example perl
> scripts using the DBI module.
>
> Is there a solution ?
>
> Thanks a lot.
> --
> Aymeric Duclert
> aduclert_at_freesurf.fr
Received on Mon Aug 14 2000 - 21:00:34 CDT