🚀 go-pugleaf

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 

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@oracle.com> wrote in message
news:agaa3m0ahj@drn.newsguy.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: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
>

...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@videotron.ca> 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" <tkyte@oracle.com> wrote in message
> news:agaa3m0ahj@drn.newsguy.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: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
> >
>
>

"AV" <avek_nospam@videotron.ca> 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
> 

Alex:

Right after I posted my question, I also came up with
exactly the same solution that you suggest (hey, great minds
think alike ;-) ).

My PL/SQL script uses a table called 'tempGUI' which is populated
one record at a time, and its contents are removed after being sent
to the client (actually, the deleting the old table is the very
first thing done).  The obvious problem is name collision.
What if two clients access the stored procedure at the same time?
Is there any way to have a private table (visible by only one
instance of the stored procedure)?  Or even better, is there some
sort of RAM-based table?

BTW: I intend to implement the PIPELINED approach as soon as I have
Oracle 9i up and running, but for now I am using the approach
that you suggested.  Again, I am worried about simultaneous access.

Thanks,

-Ramon

"Ramon F Herrera" <ramon@conexus.net> wrote in message
news:c9bc36ff.0207081836.29106794@posting.google.com...
> "AV" <avek_nospam@videotron.ca> 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
> >
>
> Alex:
>
> Right after I posted my question, I also came up with
> exactly the same solution that you suggest (hey, great minds
> think alike ;-) ).
>
> My PL/SQL script uses a table called 'tempGUI' which is populated
> one record at a time, and its contents are removed after being sent
> to the client (actually, the deleting the old table is the very
> first thing done).  The obvious problem is name collision.
> What if two clients access the stored procedure at the same time?
> Is there any way to have a private table (visible by only one
> instance of the stored procedure)?  Or even better, is there some
> sort of RAM-based table?
>
> BTW: I intend to implement the PIPELINED approach as soon as I have
> Oracle 9i up and running, but for now I am using the approach
> that you suggested.  Again, I am worried about simultaneous access.
>
> Thanks,
>
> -Ramon

Ramon,

there is in-memory "PL/SQL TABLE" object in Oracle.
Handling of such tables is a bit tricky but not rocket science.

Alternative would be exclusive locking of TempGUI table.

Alternative would be synchronizing java code assessing
this pl/sql code. This is valid ONLY if there is no other way
this stored procedure is called.

AlexV.

This is a multi-part message in MIME format.
--------------0D86AE2A413914B988FC1055
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit

This rings a bell ... I solved several similar problems waaay back in 1998 with Oracle Reports 2.5 (can't
remember server version but probably 8.x).

I created a permanent table to hold temporary data. The report would first call a PL/SQL stored procedure
from a report trigger, which performed some complex query processing and populate the summary data into
the temp table, then the report data model would contain a fairly simple set of queries to fetch from the
temp table and produce output.

Concurrency was an issue - two users could run the report concurrently, and both reports would populate
their own temp data into the table, which needed to be kept separate.

If the pre-processing is not too complex, the whole thing could be done inside one logical transaction:

SOLUTION 1:
-- Report starts up, connects to the database.
-- Trigger calls stored procedure
-- Stored procedure (which executes within same session and transaction) inserts rows into temp table,
but does not commit.
-- Report data model queries all rows in table (since no rows are ever committed, only this user's
current pending rows are returned).
-- Report issues a ROLLBACK and exits.

This was fine if the volume of inserted rows was low, all rows were uncommitted and so the table was
always "empty" and didn't need to be purged.

However, if the preprocessing gets complicated you want to be able to query the inserted rows for
debugging, so it becomes necessary to commit the rows.

SOLUTION 2:
-- Report starts up, connects to database.
-- Trigger queries database to get current session id (sorry I forgot exactly how this was done)
-- Trigger calls stored procedure and passes session id.
-- Stored procedure inserts rows into temp table, and commits. Every row inserted contains the session id
parameter.
-- Report data model queries only the rows in table WHERE SESSION_ID = :p_session_id (so only this
session's rows are returned).
-- Report deletes all rows WHERE SESSION_ID = :p_session_id, and commits. (this was commented out during
debugging).
-- Temp table might need occasional purging, due to program aborts. You could avoid this by timestamping
each row, and start your program by deleting all rows more than X days old.

You are calling your stored procedure from java, but it sounds quite close. If your pl/sql call executes
in the same transaction as your java selects, you can use solution 1. Regardless, you can use solution 2.

HTH

Martin Doherty

Ramon F Herrera wrote:

> "AV" <avek_nospam@videotron.ca> 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
> >
>
> Alex:
>
> Right after I posted my question, I also came up with
> exactly the same solution that you suggest (hey, great minds
> think alike ;-) ).
>
> My PL/SQL script uses a table called 'tempGUI' which is populated
> one record at a time, and its contents are removed after being sent
> to the client (actually, the deleting the old table is the very
> first thing done).  The obvious problem is name collision.
> What if two clients access the stored procedure at the same time?
> Is there any way to have a private table (visible by only one
> instance of the stored procedure)?  Or even better, is there some
> sort of RAM-based table?
>
> BTW: I intend to implement the PIPELINED approach as soon as I have
> Oracle 9i up and running, but for now I am using the approach
> that you suggested.  Again, I am worried about simultaneous access.
>
> Thanks,
>
> -Ramon

--------------0D86AE2A413914B988FC1055
Content-Type: text/x-vcard; charset=UTF-8;
 name="martin.doherty.vcf"
Content-Transfer-Encoding: 7bit
Content-Description: Card for Martin Doherty
Content-Disposition: attachment;
 filename="martin.doherty.vcf"

begin:vcard 
n:Doherty;Martin
tel;work:+1 650 506-8398
x-mozilla-html:FALSE
url:http://industries.oraclecorp.com
org:Oracle Corporation;Industry Solutions
version:2.1
email;internet:martin.doherty@oracle.com
title:Senior Software Specialist
adr;quoted-printable:;;Mail Stop C3038=0D=0A10 Twin Dolphin Dr=0D=0A;Redwood Shores;CA;94065;USA
fn:Martin Doherty
end:vcard

--------------0D86AE2A413914B988FC1055--

> > My PL/SQL script uses a table called 'tempGUI' which is populated
> > one record at a time, and its contents are removed after being sent
> > to the client (actually, the deleting the old table is the very
> > first thing done).  The obvious problem is name collision.
> > What if two clients access the stored procedure at the same time?
> > Is there any way to have a private table (visible by only one
> > instance of the stored procedure)?  Or even better, is there some
> > sort of RAM-based table?
> >
> > BTW: I intend to implement the PIPELINED approach as soon as I have
> > Oracle 9i up and running, but for now I am using the approach
> > that you suggested.  Again, I am worried about simultaneous access.
> >
> > Thanks,
> >
> > -Ramon

  Maybe it is the REFCURSOR (just CURSOR?) you are looking for?



karl

ramon@conexus.net (Ramon F Herrera) wrote in message news:<c9bc36ff.0207081836.29106794@posting.google.com>...
> "AV" <avek_nospam@videotron.ca> 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
> > 
> 
> Alex:
> 
> Right after I posted my question, I also came up with
> exactly the same solution that you suggest (hey, great minds
> think alike ;-) ).
> 
> My PL/SQL script uses a table called 'tempGUI' which is populated
> one record at a time, and its contents are removed after being sent
> to the client (actually, the deleting the old table is the very
> first thing done).  The obvious problem is name collision.
> What if two clients access the stored procedure at the same time?
> Is there any way to have a private table (visible by only one
> instance of the stored procedure)?  Or even better, is there some
> sort of RAM-based table?
> 
> BTW: I intend to implement the PIPELINED approach as soon as I have
> Oracle 9i up and running, but for now I am using the approach
> that you suggested.  Again, I am worried about simultaneous access.
> 
> Thanks,
> 
> -Ramon

You may want to look at global temporary tables. They can be accessed
by the same name by many sessions, yet data there will be private to
each session. After session completes, data is gone. Using PL/SQL
tables (similair to an array)is also a possibility. You can create a
function which will populate such a table with results of some query
and then return this table. With a little trick you can even select
from this function.

    Hth, Igor Izvekov.

On 9 Jul 2002 14:01:17 -0700
igoriz@cmtk.net (Igor Izvekov) wrote:

> You may want to look at global temporary tables. They can be accessed
> by the same name by many sessions, yet data there will be private to
> each session. After session completes, data is gone. Using PL/SQL
> tables (similair to an array)is also a possibility. You can create a
> function which will populate such a table with results of some query
> and then return this table. With a little trick you can even select
> from this function.

I still think he is looking for the CURSOR. That way you don't have to bother about making tempoarary tables and who knows what to slow down the already so slow Oracle. It is done the same way with PostgreSQL as with Oracle and I've done it with booth of them.

Look for PL/SQL CURSOR or PostgreSQL RefCursor

You might want to make your own ProxyCallableStatement and ProxyConnection to get everything run smooth.


karl