Re: Stored Procedures and Result Sets

> This really relates to the problem that we have logic that is too
> complicated to embed in a query or a view, and we cannot return a
> result set from an Oracle PL/SQL procedure. If we cannot use
> arrays, does anyone have an alternative solution for returning
> multiple rows from an Oracle procedure?

You can't return a result set from a stored proc? Why not:

create or replace package foo_pkg is
  type num_curs is ref cursor;

  procedure foo_proc(nums in out num_curs); end foo_pkg;

create or replace package body foo_pkg is   procedure foo_proc(nums in out num_curs) is   begin
    open nums for select num from number_tbl;   end foo_proc;
end foo_pkg;

I'm not sure how to bind to num_curs from Java/JDBC, but it shouldn't be impossible ;) If you must, I suppose you could strongly type the ref cursor, but using the generic ref cursor type is more flexible.

-Tony- Received on Mon May 18 1998 - 00:00:00 CDT