Path: news.easynews.com!easynews!priapus.visi.com!news-out.visi.com!hermes.visi.com!newsfeed.news2me.com!nntp1.phx1.gblx.net!nntp.gblx.net!nntp.gblx.net!pln-w!spln!dex!extra.newsguy.com!newsp.newsguy.com!drn
From: Thomas Kyte <tkyte@oracle.com>
Newsgroups: comp.databases.oracle.server,comp.databases.oracle.misc,borland.public.jbuilder.database,comp.lang.java.databases,comp.lang.java.programmer
Subject: Re: How to aggregate PL/SQL records into a Result Set?
Date: 7 Jul 2002 13:59:34 -0700
Organization: Oracle
Lines: 69
Message-ID: <agaa3m0ahj@drn.newsguy.com>
References: <c9bc36ff.0207071227.141865fc@posting.google.com>
NNTP-Posting-Host: p-172.newsdawg.com
X-Newsreader: Direct Read News 2.96
Xref: easynews comp.databases.oracle.server:153324 comp.databases.oracle.misc:83910 borland.public.jbuilder.database:11988 comp.lang.java.databases:38748 comp.lang.java.programmer:378130
X-Received-Date: Sun, 07 Jul 2002 14:19:52 MST (news.easynews.com)

In article <c9bc36ff.0207071227.141865fc@posting.google.com>, ramon@conexus.net
says...
>
>Server Side:
>SPARC Solaris 8
>Oracle 9i RDBMS, PL/SQL
>
>Client Side:
>Sun Java 1.4
>Borland JBuilder 7 SE
>MS Windows XP
>
>
>My application is almost ready, but it is still missing a
>last, and very critical link.  The objective is to have a
>Java GUI client in which the user types a value, clicks on
>a button and the program makes a JDBC call to a remote PL/SQL
>stored function.  The returned values (it's a read-only app)
>are displayed, each record on a separate tabbed panel.
>
>The Java app is basically ready and the Oracle stored function
>too, but I still have to make them talk properly to each other.
>
>The basic problem is that the Java front end expects to receive a
>Result Set in the way documented here (pretty standard stuff):
>
>    http://osi.oracle.com/~tkyte/ResultSets/index.html
>
>However, I don't know how to write a stored function which
>will return a Result Set.
>
>Here's the catch: my PL/SQL subprogram has to select-query a
>whole bunch of tables (many tables BUT all of them have exactly
>the same layout).  Since the query is done on a unique key
>WHERE condition, each query returns precisely zero or one records.
>So, from the server side I have several independent records
>which I need to aggregate somehow, and convert them in a
>Result Set which is the return variable of the stored function.
>
>So how can I "build" piecewise, -adding a record at a time-
>a Result Set in PL/SQL?
>
>
>TIA,
>
>-Ramon F. Herrera
>
>ps:
>
>Is there something like this?:
>
>    ResultSet[1] := v_Record1;
>    ResultSet[2] := v_Record2;
>    ResultSet[3] := v_Record3;
>    RETURN ResultSet;


see

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:4447489221109

for an example of a pipelined function... sounds like what you want.

--
Thomas Kyte (tkyte@oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp