Topics

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?


Andy
 
Edited

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


Andy
 

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.


Andy
 

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