I've been trying to determine how to call an Oracle 11g stored object that
returns a REF CURSOR.
So far, I've tried to create a stored function the returns a SYS_REFCURSOR
and also a stored procedure that uses an OUT parameter of type
SYS_REFCURSOR.
Regardless how I attempt to perform this action to obtain a result set, I
cannot seem to obtain the usable result set within my application.
For instance:
//SQL
CREATE OR REPLACE PROCEDURE P_RETRIEVE_SET( RESULTS OUT SYS_REFCURSOR ) IS
BEGIN
OPEN RESULTS FOR SELECT * FROM MY_TABLE;
END ;
//Java
EntityManager em = MyEntityFactory.createEntityManager(MY_PERSISTANCE_UNIT);
Query qry = em.createNativeQuery("P_RETRIEVE_SET(?)");
List resultList = qry.getSingleResult();
for (Object o : resultList) {
System.out.println(o);
}
I've tried this same type of logic using the following:
1) NamedNativeQueries
2) Stored Function that returns SYS_REFCURSOR
3) CALL P_RETRIEVE_SET(?)
The best I ever got was a return Vector from the Query that contained a
single element of type OracleResultSetImpl. However, this result set was
close and unusable for iteration or meta-data retrieval.
I've looked for several hours online to find an example of this
functionality with no avail. Any assistance would be appreciated.
I have found a similar issue in OPENJPA-918, but this only applied to
2.0.0M2, not 1x.
https://issues.apache.org/jira/browse/OPENJPA-918?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
Thank you,
Seth
-----
Seth Jackson
--
View this message in context:
http://n2.nabble.com/OpenJPA-1-2-1-Oracle-Stored-Procedures-and-OUT-parameters-tp4428715p4428715.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.
