`Type cannot be null` exception when trying to run out Stored Procedure using Spring Data JPA

I am trying to invoke a Stored Procedure whose signature looks like the following:

CREATE OR REPLACE PROCEDURE FIND_FIRST_BOOKMARK_GT(bookmark IN NUMBER, cur OUT SYS_REFCURSOR)

I am using Spring-Data JPA and have tried a number of different variants, but all of them follow more or less the following pattern. My entity model is decorated as follows:

@NamedStoredProcedureQuery(name = "Response.findFirstBookmarkGreaterThan", procedureName = "FIND_FIRST_BOOKMARK_GT",
    resultClasses = Response.class,
    parameters = {
        @StoredProcedureParameter(name = "bookmark", mode = ParameterMode.IN, type = Long.class),
        @StoredProcedureParameter(name = "cur", mode = ParameterMode.REF_CURSOR, type = void.class)
    })
})

Then I have my repository which looks like the following:

@Repository
public interface ResponseRepository extends CrudRepository<Response, Long>{
    @Procedure("Response.findFirstBookmarkGreaterThan")
    Response findFirstBookmarkGreaterThan(@Param("bookmark") Long bookmark);
}

I have followed the instructions from quite a few examples, but I am always left with the same error, namely:

java.lang.IllegalArgumentException: Type cannot be null
at org.hibernate.procedure.internal.AbstractParameterRegistrationImpl.setHibernateType(AbstractParameterRegistrationImpl.java:182) ~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]
at org.hibernate.procedure.internal.AbstractParameterRegistrationImpl.<init>(AbstractParameterRegistrationImpl.java:131) ~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]
at org.hibernate.procedure.internal.AbstractParameterRegistrationImpl.<init>(AbstractParameterRegistrationImpl.java:140) ~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]
at org.hibernate.procedure.internal.AbstractParameterRegistrationImpl.<init>(AbstractParameterRegistrationImpl.java:97) ~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]
at org.hibernate.procedure.internal.NamedParameterRegistration.<init>(NamedParameterRegistration.java:41) ~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]
at org.hibernate.procedure.internal.ProcedureCallImpl.registerParameter(ProcedureCallImpl.java:344) ~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]
at org.hibernate.jpa.internal.StoredProcedureQueryImpl.registerStoredProcedureParameter(StoredProcedureQueryImpl.java:152) ~[hibernate-entitymanager-4.3.11.Final.jar:4.3.11.Final]
at org.springframework.data.jpa.repository.query.StoredProcedureJpaQuery.newAdhocStoredProcedureQuery(StoredProcedureJpaQuery.java:175) ~[spring-data-jpa-1.9.4.RELEASE.jar:na]
at org.springframework.data.jpa.repository.query.StoredProcedureJpaQuery.createStoredProcedure(StoredProcedureJpaQuery.java:130) ~[spring-data-jpa-1.9.4.RELEASE.jar:na]
at org.springframework.data.jpa.repository.query.StoredProcedureJpaQuery.doCreateQuery(StoredProcedureJpaQuery.java:89) ~[spring-data-jpa-1.9.4.RELEASE.jar:na]
at org.springframework.data.jpa.repository.query.StoredProcedureJpaQuery.createQuery(StoredProcedureJpaQuery.java:80) ~[spring-data-jpa-1.9.4.RELEASE.jar:na]
......

Any ideas what is wrong here?

Answer

I was not able to get this syntax working as such, however, the notion of retrieving from a stored procedure is effectively a shorthand for selecting from (possibly a view) mixed with applying some input based function. To that effect, I have replaced my stored procedure with a function / table that yields the same effect.

@Query("SELECT r FROM RESPONSES r WHERE BOOKMARK = FIND_FIRST_BOOKMARK_GT(:bookmark)")
Response findFirstBookmarkGreaterThan(@Param("bookmark") Long bookmark);

This uses the @Query syntax in conjunction with a table and a function, whose overall logic is identical to calling the stored procedure I had intended. Although a little more difficult to follow perhaps, the syntax is at least much shorter than using the @NamedStoredProcedureQuery.

The function does the same thing the stored procedure originally did EXCEPT for the last part which was a SELECT INTO, this is now encapsulated as part of the @Query.

Advertisement

Answer