Create ResultSet "from scratch" in Oracle Java Stored Procedure?

Create ResultSet "from scratch" in Oracle Java Stored Procedure?

Post by Jim Garris » Fri, 03 Sep 2004 02:10:05


I need to create a ResultSet in an Oracle Java Stored Procedure and
return it to a PL/SQL caller. I've done quite a bit of research in
Oracle's manuals and on the Web, and have found lots of references to
going the other direction (PL/SQL passing/returning REF CURSOR to
Java), and Java returning an existing ResultSet (obtained from the
databse) as a REF CURSOR to PL/SQL. Neither of these is what I'm
looking for.

My Java stored procedure will do significant data reduction on a large
database, and then needs to return a large result set to the caller,
which will be in PL/SQL. To the PL/SQL caller, the Java stored
procedure needs to look like a "table function", but I need to create
the result set from scratch in my Java code. What I think I need is a
documented skeleton ResultSet implementation that I can extend.

Anyone know if this is available anywhere, or have pointers to
documentation?

Jim Garrison
XXXX@XXXXX.COM
 
 
 

Create ResultSet "from scratch" in Oracle Java Stored Procedure?

Post by Jim Kenned » Fri, 03 Sep 2004 11:30:43


Did you look at the pipelined property of a function?
Jim

 
 
 

Create ResultSet "from scratch" in Oracle Java Stored Procedure?

Post by Angu » Fri, 03 Sep 2004 12:01:08


Use a temporary table or a nested table, or some other PL/SQL
collection type (there are several). See the "Collection Types"
chapter in the PL/SQL manual.

Good Luck,
Avi.
 
 
 

Create ResultSet "from scratch" in Oracle Java Stored Procedure?

Post by pratap_fi » Fri, 03 Sep 2004 13:28:36

I assume that your Java procedure is working on some huge data in the
database, and then generating a set of rows that you want to pass on
to PL/SQL.
From Java you can batch insert the data in a temporary table. PL/SQL
can then read this table. The insert overhead in the temporary table
will be very less.

If you are using Oracle 9i you may use pipelined functions can also be
used as they they can be written in Java. But I don't know much about
Java.

Pratap
 
 
 

Create ResultSet "from scratch" in Oracle Java Stored Procedure?

Post by Jim Garris » Sat, 04 Sep 2004 04:10:38


Sounds like what I need, except I can't find in Oracle's
documentation the Java equivalent of the PL/SQL "PIPE ROW"
statement. You say pipelined functions can be written in Java...
do you have any pointers to documentation?

Thanks

Jim Garrison
XXXX@XXXXX.COM