🚀 go-pugleaf

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;