Path: news.easynews.com!easynews!ps01-sjc1!news.webusenet.com!cyclone.bc.net!snoopy.risq.qc.ca!wesley.videotron.net!wagner.videotron.net.POSTED!not-for-mail From: "AV" Newsgroups: comp.databases.oracle.server,comp.databases.oracle.misc,borland.public.jbuilder.database,comp.lang.java.databases,comp.lang.java.programmer References: <457W8.6770$Qt6.589048@wagner.videotron.net> Subject: Re: How to aggregate PL/SQL records into a Result Set? Lines: 103 X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2600.0000 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 Message-ID: Date: Sun, 7 Jul 2002 22:54:18 -0400 NNTP-Posting-Host: 24.201.21.248 X-Complaints-To: abuse@videotron.ca X-Trace: wagner.videotron.net 1026096855 24.201.21.248 (Sun, 07 Jul 2002 22:54:15 EDT) NNTP-Posting-Date: Sun, 07 Jul 2002 22:54:15 EDT Xref: easynews comp.databases.oracle.server:153340 comp.databases.oracle.misc:83915 borland.public.jbuilder.database:11990 comp.lang.java.databases:38751 comp.lang.java.programmer:378155 X-Received-Date: Sun, 07 Jul 2002 19:51:37 MST (news.easynews.com) ...may be even Oracle VIEW can be used with one big-big sql with bunch of joined selects.... ...can be ugly... AlexV. "AV" wrote in message news:457W8.6770$Qt6.589048@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" wrote in message > news:agaa3m0ahj@drn.newsguy.com... > > In article , > 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: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 > > > >