Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> How to aggregate PL/SQL records into a Result Set?
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 timea 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;