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

[Jim Garrison]

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
jh*@athensgroup.com

[Jim Kennedy]

"Jim Garrison" <jh*@athensgroup.com> wrote in message
news:Ap********************@giganews.com...

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
jh*@athensgroup.com

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

[Pratap]

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

[Jim Garrison]

Pratap wrote:

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

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
jh*@athensgroup.com