spring-data-jpa 1.11.16 stored procedure with curs

Question

i'm unable to use stored procedure with cursor in spring-data-jpa (version 1.11.16) and an oracle server.

The Repository:

@Repository
public interface GeoRegionRepository extends CrudRepository<PocRegions, String> {

    @Procedure(name = "PocRegions.getRegion", procedureName = "POC_PKG_GEO.PRO_RETURN_REGION")
    List<PocRegions> getRegion(@Param("id_region") BigDecimal regionId);
}

The annotation on the entity:

@Entity
@Table(name = "POC_REGIONS")
@NamedStoredProcedureQueries({
        @NamedStoredProcedureQuery( name = "PocRegions.getRegion", procedureName = "POC_PKG_GEO.PRO_RETURN_REGION",
                resultClasses = PocRegions.class ,parameters = {
                @StoredProcedureParameter(mode = ParameterMode.IN, name = "id_region", type = BigDecimal.class),
                @StoredProcedureParameter(mode = ParameterMode.REF_CURSOR, name = "o_cursor", type = Void.class)})
})

that's the java response when i invoke:

geoRegionRepo.getRegion(id)

↓

2018-12-11 13:07:26.047 DEBUG 24164 --- [nio-9090-exec-2] org.hibernate.SQL : {call POC_PKG_GEO.PRO_RETURN_REGION(?,?)} Hibernate: {call POC_PKG_GEO.PRO_RETURN_REGION(?,?)} 2018-12-11 13:07:27.410 TRACE 24164 --- [nio-9090-exec-2] o.h.type.descriptor.sql.BasicBinder : binding parameter [id_region] as [NUMERIC] - [1] 2018-12-11 13:07:27.658 WARN 24164 --- [nio-9090-exec-2] .m.m.a.ExceptionHandlerExceptionResolver : Resolved [java.lang.UnsupportedOperationException: org.hibernate.dialect.Oracle9iDialect does not support resultsets via stored procedures]

I'm not sure if is possible to use cursor with this old version of spring-data or not. What sould i do to use spring-data-jpa and call the procedure?

Thank you

EDIT 1:

i added into my application.properties the following line:

spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.Oracle10gDialect

and added that to the pom (excluding hibernate conflict into spring-data-jpa dependency's declariation):

<dependency>
    <groupId>org.hibernate</groupId>
    <artifactId>hibernate-tools</artifactId>
    <version>${hibernate.version}</version>
</dependency>

Answer 1

Most likely the problem is not the version of spring-data, but Hibernate version and/or configuration.

Check carefully the error message:

java.lang.UnsupportedOperationException: org.hibernate.dialect.Oracle9iDialect does not support resultsets via stored procedures

The error says that the Oracle9 dialect used by Hibernate doesn't support reading data from stored procedures. So, the problem's origin is Hibernate, not Spring Data.

If you try a quick search using the error message you'll get this useful thread:

java.lang.UnsupportedOperationException: org.hibernate.dialect.Oracle10gDialect does not support resultsets via stored procedures

According to this post, I will probably need to upgrade your hibernate version to 5.1 and the dialect version (oracle 9 to 10).