Create Sample Stored Procedure with cursor

[marcosjunqueira]

Create Sample Stored Procedure with cursor

[odrotbohm]

Can you elaborate on what you'd like to see? The examples cover all the stored procedure support we offer currently.

[marcosjunqueira]

I was trying to execute a oracle procedure with spring-data-jpa and hibernate

PROCEDURE MY_PROC (
    P_ID IN NUMBER,
    P_PERIOD IN VARCHAR2,
    P_LIMIT IN NUMBER,
    P_CURSOR OUT T_CURSOR);

MyEntity.java

@NamedStoredProcedureQuery(
        name = "myProc",
        procedureName = "MY_PROC",
        resultClasses = ResultEntity.class,
        parameters = {
            @StoredProcedureParameter(mode = ParameterMode.IN, type = Long.class),
            @StoredProcedureParameter(mode = ParameterMode.IN, type = String.class),
            @StoredProcedureParameter(mode = ParameterMode.IN, type = Long.class),
            @StoredProcedureParameter(mode = ParameterMode.REF_CURSOR, type = void.class)

MyRepository.java

@Procedure(name = "myProc", procedureName = "MY_PROC")
    List<ResultEntity> execMyProc(Long userId,String period,Long idClientLimit);

Using Hibernate with JPA I had no success.
Then I discovered that the OracleDialect did not implement all the methods needed to perform the procedure, and implemented a custom dialect.
I was able run the procedure programmatically

StoredProcedureQuery query = entityManager.createNamedStoredProcedureQuery("extractWebUser");
query.setParameter(1, userId);
query.setParameter(2, period);
query.setParameter(3, idClientLimit);
query.execute();
List resultList = query.getResultList();

But on spring-data we need to use getOutputParameterValue Method instead of getResultList

Object outputParameterValue = query.getOutputParameterValue(4);

Then I discovered that the hibernate does not support REF_CURSOR

org.hibernate.procedure.internal.AbstractParameterRegistrationImpl.java

...
// we could use the Types#REF_CURSOR added in Java 8, but that would require requiring Java 8...
...
public T extract(CallableStatement statement) {
...
else if ( mode == ParameterMode.REF_CURSOR ) {
            throw new ParameterMisuseException( "REF_CURSOR parameters should be accessed via results" );
        }
...

Finally, I moved to the EclipseLink and it worked perfectly.

[odrotbohm]

I don't thin this is an issue for the examples repo as it seems we're still lacking support for cursor parameters. Would you mind opening a ticket in our issue tracker?

[marcosjunqueira]

I was thinkin that i was doing something wrong, but not. I'll open a issue. Thanks for attention @olivergierke.

[marcosjunqueira]

https://jira.spring.io/browse/DATAJPA-652

[vishnudevk]

This issue is still there, anyone has any luck on this?