Oracle SQLJ limitation with java.sql.ResultSet input parameter ...

[Giovanni Azua]

Hello all,

I have a PL/SQL API which I call from a Java J2EE business
layer, instead of running any SQL statement (directly or
indirectly e.g. EJB-SQL) from any of my Java tiers.

One of the PL/SQL API functions purpose is to return to the
business layer the results of a query, a REF CURSOR i.e.
sys_refcursor which is a very handy, reusable and scalable
way to send results to Java, then my Java DAO classes (using
JPublisher generated SQLJ as basement) on the business layer
take care of transforming the java.sql.ResultSet into Data
Transfer Objects which are finally sent to the presentation
tier, and this works ok.

Now I have a new requirement which is that every execution of
that PL/SQL API function should be logged for auditing purposes,
the log must include input parameters to the API as well as
some of the results i.e. few top records.

This became a problem because I can not fetch any records from
PL/SQL otherwise would have to reopen the cursor which means
executing twice! I also tried creating yet another PL/SQL API
function exactly for that purpose of logging and trying to
send java.sql.ResultSet from Java as input parameter to PL/SQL
is not supported by SQLJ (JPublisher generated files).

I would not like to make any exceptions with the PL/SQL API
(not using SQLJ), neither execute any SQL from Java, and still
not have to create yet another kind of Transfer Object type (Oracle
object type) which would sum up to the DAO coupling to Oracle
just for passing aggregated parameters from Java to Oracle e.g.

Any ideas?

Thanks in advance,
Best Regards,
Giovanni

PS: I appologize for the cross-post but there is not this
specialized which group about Oracle SQLJ.