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

Question

how to solve the following error I am using JPA 2.1 widfly Application Server 8.0 and Oracle 11g bd

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

Deputy code:

        StoredProcedureQuery storedProcedureQuery=em.createStoredProcedureQuery("SIB_PQ_SENA.CREAR_VENTAS",Venta.class);
    storedProcedureQuery.registerStoredProcedureParameter("P_ID_EMPLEADO", Integer.class , ParameterMode.IN);
    storedProcedureQuery.registerStoredProcedureParameter("P_ID_CLIENTE", Integer.class, ParameterMode.IN);
    storedProcedureQuery.registerStoredProcedureParameter("P_ID_SUCURSAL", Integer.class, ParameterMode.IN);
    storedProcedureQuery.registerStoredProcedureParameter("CURSORRETURN", void.class, ParameterMode.REF_CURSOR);
    storedProcedureQuery.setParameter("P_ID_EMPLEADO", idEmpleado);
    storedProcedureQuery.setParameter("P_ID_CLIENTE", idCliente);
    storedProcedureQuery.setParameter("P_ID_SUCURSAL", idSucursal);
    storedProcedureQuery.execute();

Answer 1

Wildfly 8 uses Hibernate 4.3.

This issue was caused by HHH-9286 which got fixed in Hibernate 5.1.0 and 5.0.8.

You need to upgrade Hibernate to get it fixed. To prove it, you can run the examples from this blog post. All the examples are on GitHub, so you can test against any Hibernate version you use.

Answer 2

After struggling a lot with this issue we finally found a solution and it is simple.

Following is my pom.xml,

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>codeshare</groupId>
    <artifactId>statement-service</artifactId>
    <version>1.0</version>
    <packaging>jar</packaging>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>1.5.8.RELEASE</version>
        <relativePath/>
    </parent>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <java.version>1.8</java.version>
        <docker.image.prefix>docker_img</docker.image.prefix>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.cloud</groupId>
            <artifactId>spring-cloud-starter-eureka</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-actuator</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.cloud</groupId>
            <artifactId>spring-cloud-starter-hystrix</artifactId>
        </dependency>
        <!--data access JPA-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
            <version>2.1.0.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-core</artifactId>
            <version>5.2.7.Final</version>
        </dependency>
        <!--<dependency>-->
            <!--<groupId>org.hibernate</groupId>-->
            <!--<artifactId>hibernate-envers</artifactId>-->
            <!--<version>5.2.11.Final</version>-->
        <!--</dependency>-->
        <dependency>
            <groupId>org.hibernate.common</groupId>
            <artifactId>hibernate-commons-annotations</artifactId>
            <version>5.1.0.Final</version>
        </dependency>
        <dependency>
            <groupId>org.hibernate.validator</groupId>
            <artifactId>hibernate-validator</artifactId>
            <version>6.0.7.Final</version>
        </dependency>
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-entitymanager</artifactId>
            <version>5.2.14.Final</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-tx</artifactId>
            <version>5.0.10.RELEASE</version>
        </dependency>


        <!--end-->
        <dependency>
            <groupId>io.jsonwebtoken</groupId>
            <artifactId>jjwt</artifactId>
            <version>0.6.0</version>
        </dependency>
        <dependency>
            <groupId>org.mongodb</groupId>
            <artifactId>mongo-java-driver</artifactId>
            <version>3.6.2</version>
        </dependency>
        <dependency>
            <groupId>com.oracle</groupId>
            <artifactId>ojdbc7</artifactId>
            <version>1.0</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>

    </dependencies>

    <dependencyManagement>
        <dependencies>
            <dependency>
                <groupId>org.springframework.cloud</groupId>
                <artifactId>spring-cloud-dependencies</artifactId>
                <version>Dalston.SR1</version>
                <type>pom</type>
                <scope>import</scope>
            </dependency>
        </dependencies>
    </dependencyManagement>

    <build>
        <finalName>statement-service</finalName>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <mainClass>Application</mainClass>
                    <addResources>true</addResources>
                </configuration>
                <executions>
                    <execution>
                        <goals>
                            <goal>repackage</goal>
                        </goals>
                    </execution>
                </executions>
            </plugin>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <configuration>
                    <source>1.8</source>
                    <target>1.8</target>
                </configuration>
            </plugin>
            <plugin>
                <groupId>com.spotify</groupId>
                <artifactId>dockerfile-maven-plugin</artifactId>
                <version>1.3.6</version>
                <configuration>
                    <repository>${docker.image.prefix}/${project.artifactId}</repository>
                    <buildArgs>
                        <JAR_FILE>target/${project.build.finalName}.jar</JAR_FILE>
                    </buildArgs>
                </configuration>
            </plugin>
        </plugins>
    </build>
</project>

and this has to be added to your property file and that's it spring.jpa.properties.hibernate.dialect: org.hibernate.dialect.Oracle10gDialect (* ':' should be replaced with '=' if you are using a application.property , i'm using application.yml)

Hope it helps someone.