0

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>
osharko
  • 204
  • 3
  • 19

1 Answers1

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).

Vitor Santos
  • 581
  • 4
  • 15
  • Adding the 5.3.7.Final versione of hibernate-core, wich last version could I integrate into my project of Oracle dialect? actually i'm using: ojdbc7 12.1.0.2 – osharko Dec 11 '18 at 13:56
  • It looks like you already upgraded the dialect do Oracle 10 when you changed your application.properties file. – Vitor Santos Dec 11 '18 at 15:05
  • yes, i founded the answer after my comment :) i'm made another question, if you could help me, it would be great. https://stackoverflow.com/questions/53727000/spring-data-jpa-not-casting-from-oracle-jdbc-driver-forwardonlyreswultset thank you – osharko Dec 11 '18 at 15:10