How to call oracle stored procedure with sys_refcursor output in Java JPA DataNucleus?
|
V MANIKANTHAN
When I try a standard code I get the following error message. javax.persistence.PersistenceException: Error encountered when extracting results for SQL query "get_person_list_sp" at org.datanucleus.api.jpa.NucleusJPAHelper.getJPAExceptionForNucleusException(NucleusJPAHelper.java:316) at org.datanucleus.api.jpa.JPAStoredProcedureQuery.getResultList(JPAStoredProcedureQuery.java:305) at org.example.jpa.JPANamedSPTest.main(JPANamedSPTest.java:17) Caused by: java.sql.SQLException: Missing IN or OUT parameter at index:: 1 at oracle.jdbc.driver.OraclePreparedStatement.processCompletedBindRow(OraclePreparedStatement.java:1937) at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3657) at oracle.jdbc.driver.T4CCallableStatement.executeInternal(T4CCallableStatement.java:1358) at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3778) at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4251) at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1081) at org.datanucleus.store.rdbms.datasource.dbcp2.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:198) at org.datanucleus.store.rdbms.datasource.dbcp2.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:198) at org.datanucleus.store.rdbms.query.StoredProcedureQuery.performExecute(StoredProcedureQuery.java:444) at org.datanucleus.store.rdbms.query.StoredProcedureQuery.executeQuery(StoredProcedureQuery.java:143) at org.datanucleus.store.query.Query.executeWithArray(Query.java:1862) at org.datanucleus.store.query.Query.execute(Query.java:1844) at org.datanucleus.api.jpa.JPAStoredProcedureQuery.getResultList(JPAStoredProcedureQuery.java:288) ... 1 more I'm able to run the same code using Hibernate. Does it mean DataNucleus does not support ParameterMode.REF_CURSOR?
|
|
|
|
It means you got an error message from your JDBC driver for some stored proc.
What that stored proc is, how you invoked it etc you don't show so not sure how you expect anyone to comment on that. Look through the log for info, find the code (StoredProcedureQuery.java) and look at the equivalent calls to JDBC CallableStatement. Then compare those with what you do if you were to invoke the exact same proc using JDBC
|
|
|
|
V MANIKANTHAN
Here is the Oracle SP with Sys_RefCursor:
CREATE OR REPLACE PROCEDURE get_person_list_sp(
src OUT SYS_REFCURSOR)
AS
BEGIN
OPEN src FOR
SELECT
id,
first_name,
last_name
FROM person
ORDER BY
first_name,
last_name;
END; Here is the Java JPA code: StoredProcedureQuery query = em.createStoredProcedureQuery("get_person_list_sp")
.registerStoredProcedureParameter(
"src",
Person.class,
ParameterMode.REF_CURSOR
);
query.execute();
List<Object[]> result = query.getResultList();
for (Object[] o : result) {
System.out.println(o[0].toString());
System.out.println(o[1].toString());
System.out.println(o[2].toString());
}
I tried with Class.class but the error is same. void.class did not compile. I'm able to run another stored procedure with IN and OUT parameter only ang get the result. If you have any sample code for ParameterMode.REF_CURSOR can you post it here. If you can post any pointers to it also fine. I will go through it. Regarding SQL output generated I set few properties. But did not get any output in the log file other than the enhancer steps. 2020-01-02 09:06:04 INFO Enhancer:82 - DataNucleus Enhancer (version 5.2.2) for API "JPA"
2020-01-02 09:06:04 INFO Enhancer:82 - Writing class file "C:\SWWorkSpace\java\JPADataNucleusTest\target\classes\org\example\jpa\Person.class" with enhanced definition
2020-01-02 09:06:04 INFO Enhancer:82 - ENHANCED (Persistable) : org.example.jpa.Person
2020-01-02 09:06:04 INFO Enhancer:82 - DataNucleus Enhancer completed with success for 1 classes. Timings : input=63 ms, enhance=39 ms, total=102 ms. Consult the log for full details
May I know which properties to set for getting the SQL generated into the log file? Is there any setting in persistence.xml file for this? persistence.xml file: <persistence-unit name="JPADataNucleusTest" transaction-type="RESOURCE_LOCAL">
<provider>org.datanucleus.api.jpa.PersistenceProviderImpl</provider>
<class>org.example.jpa.Person</class>
<exclude-unlisted-classes>true</exclude-unlisted-classes>
<properties>
<property name="javax.persistence.jdbc.driver" value="oracle.jdbc.OracleDriver"/>
<property name="javax.persistence.jdbc.url" value="jdbc:oracle:thin:@localhost:1521:ORCL"/>
<property name="javax.persistence.jdbc.user" value="c##test"/>
<property name="javax.persistence.jdbc.password" value="T3st1234"/>
<property name="datanucleus.schema.autoCreateAll" value="true"/>
</properties>
</persistence-unit>
log4j.properties file: # DataNucleus
#log4j.appender.A1=org.apache.log4j.FileAppender
#log4j.appender.A1.File=log/datanucleus.log
#log4j.appender.A1.layout=org.apache.log4j.PatternLayout
#log4j.appender.A1.layout.ConversionPattern=%d{HH:mm:ss,SSS} (%t) %-5p [%c] - %m%n
#log4j.appender.A1.Threshold=ALL
#log4j.category.DataNucleus=ALL
# Categories
# Each category can be set to a "level", and to direct to an appender
# Default to DEBUG level for all categories
#log4j.logger.DataNucleus=DEBUG, A1
# DataNucleus Categories
#log4j.category.DataNucleus.JPA=DEBUG, A1
#log4j.category.DataNucleus.JDO=INFO, A1
#log4j.category.DataNucleus.Cache=INFO, A1
#log4j.category.DataNucleus.MetaData=INFO, A1
#log4j.category.DataNucleus.General=INFO, A1
#log4j.category.DataNucleus.Transaction=INFO, A1
#log4j.category.DataNucleus.Datastore=DEBUG, A1
#log4j.category.DataNucleus.Datastore.Native=DEBUG, A1
#log4j.category.DataNucleus.Query=DEBUG, A1
#log4j.category.DataNucleus.ValueGeneration=DEBUG, A1
#log4j.category.DataNucleus.Enhancer=INFO, A1
#log4j.category.DataNucleus.SchemaTool=INFO, A1
|
|
|
|
Logging is defined in http://www.datanucleus.org:15080/products/accessplatform_5_2/jdo/persistence.html#logging and you simply set DataNucleus.Datastore and subcategories for datastore communication (such as DDL or SQL).
Get the code as previously advised. Look at https://github.com/datanucleus/datanucleus-rdbms/blob/master/src/main/java/org/datanucleus/store/rdbms/query/StoredProcedureQuery.java#L316 and https://github.com/datanucleus/datanucleus-rdbms/blob/master/src/main/java/org/datanucleus/store/rdbms/query/StoredProcedureQuery.java#L451 and consider adding something like this || param.getMode() == StoredProcQueryParameterMode.REF_CURSOR to those lines. I don't use REF_CURSOR so its up to people to try things and contribute so that their requirements are catered for (but that's why open source exists, after all).
|
|
|
|
V MANIKANTHAN
Thanks for your assistance. I will look at this code.
|
|
|
|
V MANIKANTHAN
I made the changes as suggested.
Then it broke at different place. Getting data type set correctly. I went through Java and Oracle SQL type and used it. Then it broke when retrieving the data. I'm not sure how to proceed further.
|
|
|
|
As said before, work out what JDBC calls you would have made using CallableStatement if you were doing the same thing, and compare with the calls made by DataNucleus
|
|
|