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

From: Giovanni Azua <bravegag_at_hotmail.com>
Date: Tue, 7 Dec 2004 13:53:57 +0100
Message-ID: <31lnpeF3b1nhbU1_at_individual.net>



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. Received on Tue Dec 07 2004 - 13:53:57 CET

Original text of this message