Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: How to aggregate PL/SQL records into a Result Set?

Re: How to aggregate PL/SQL records into a Result Set?

From: AV <avek_nospam_at_videotron.ca>
Date: Sun, 7 Jul 2002 22:54:18 -0400
Message-ID: <r97W8.6937$Qt6.592514@wagner.videotron.net>


...may be even Oracle VIEW can be used with one big-big sql with bunch of joined selects....

...can be ugly...

AlexV.

"AV" <avek_nospam_at_videotron.ca> wrote in message news:457W8.6770$Qt6.589048_at_wagner.videotron.net...
> Most probably it will be somewhat slower,
> but here is a plan:
> -- create buffer table.
> -- create PL/SQL procedure that
> ---- clean buffer table
> ---- populate table from any number of selects
> -- from java code
> -- call pl/sql procedure
> -- call simplest select from buffer table
>
> this can be better and from error handling point of view
>
> AlexV.
>
> "Thomas Kyte" <tkyte_at_oracle.com> wrote in message
> news:agaa3m0ahj_at_drn.newsguy.com...
> > In article <c9bc36ff.0207071227.141865fc_at_posting.google.com>,
> ramon_at_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:444748922
> 1109
> >
> > 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
> >
>
>
Received on Sun Jul 07 2002 - 21:54:18 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US