Reading ref cursor as output parameter in a stored procedure with spring data jpa returns null

Question

I'm trying to read a refcursor in an oracle stored procedure using spring data jpa and spring boot, the stored procedure runs succesfully but the reference to the returned List is always null.

I tried using Hibernate as a JPA provider and Eclipse Link with no succed, below are the oracle ddl and java code

Oracle Table (I filled the table with some example data)

CREATE TABLE role
    (id                             NUMBER(10,0),
    name                           VARCHAR2(255 CHAR))

Oracle Stored Procedure

PROCEDURE collect_roles (role_list_o OUT SYS_REFCURSOR) IS
    ex EXCEPTION;
BEGIN
    OPEN role_list_o FOR SELECT id, name FROM role;
END;

pom.xml (the oracle jdbc driver dependency was installed in maven from a local ojdbc7.jar)

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

<groupId>org.springframework</groupId>
<artifactId>gridapp</artifactId>
<version>0.1.0</version>

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


<properties>
    <java.version>1.8</java.version>
</properties>

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>

    <!-- Oracle JDBC driver -->
    <dependency>
        <groupId>com.oracle</groupId>
        <artifactId>ojdbc7</artifactId>
        <version>12.1.0</version>
    </dependency>
</dependencies>


<build>
    <plugins>
        <plugin>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-maven-plugin</artifactId>
        </plugin>
    </plugins>
</build>
</project>

application.properties

spring.main.banner-mode=off

# Oracle settings
spring.datasource.url=jdbc:ora_string_conection
spring.datasource.username=username
spring.datasource.password=password
spring.datasource.driver-class-oracle.jdbc.driver.OracleDriver

# HikariCP settings
# spring.datasource.hikari.*

spring.datasource.hikari.connection-timeout=60000
spring.datasource.hikari.maximum-pool-size=5

Role.java

package hello;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.NamedStoredProcedureQueries;
import javax.persistence.NamedStoredProcedureQuery;
import javax.persistence.ParameterMode;
import javax.persistence.StoredProcedureParameter;
import javax.persistence.Table;

import org.springframework.data.domain.Sort.Direction;

@NamedStoredProcedureQueries({
@NamedStoredProcedureQuery(name = "findRolesViaProcedure",
    procedureName = "wfactura.collect_roles",
    resultClasses = Role.class,
    parameters = {
        @StoredProcedureParameter(mode = ParameterMode.REF_CURSOR, name = "role_list_o", type = Void.class)

    })})
@Entity
public class Role {

    @Id @GeneratedValue//
    @Column(name="ID")
    private Long id;

    @Column(name="NAME")
    private String name;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }
}

RoleRepository.java

package hello;

import java.io.Serializable;
import java.util.List;

import org.springframework.data.jpa.repository.query.Procedure;
import org.springframework.data.repository.CrudRepository;

interface RoleRepository extends CrudRepository<Role, Serializable> {

    @Procedure(name = "findRolesViaProcedure")
    List<Role> findRolesViaProcedure();

}

I call the store procedure with the next line

List<Role> roles = repo.findRolesViaProcedure();

I modified the stored procedure for inserting values in another table and i realized that the execution was succesfully, but the List roles is null even though there is data in the table.

Could anyone please help me?

Did you ever find a solution? – GabrielBB Jan 04 '20 at 15:45 — GabrielBB, Jan 04 '20 at 15:45

score 0 · Answer 1 · edited Mar 18 '22 at 14:44

0

Change

type=Void.class

to

type=ResultSet.Class

edited Mar 18 '22 at 14:44

MWiesner

8,868
11
36
70

answered Feb 24 '22 at 15:46

jean tchietcheu

1
1

score 0 · Answer 2 · answered Sep 08 '22 at 14:52

0

I solved the problem by switching to version Spring Boot 2.7.3

answered Sep 08 '22 at 14:52

Igor.D

51
5

score -1 · Answer 3 · edited Nov 06 '19 at 10:56

-1

@StoredProcedureParameter(mode = ParameterMode.REF_CURSOR, name = "role_list_o", type = Void.class)

Should be modified as : ==>

@StoredProcedureParameter(mode = ParameterMode.REF_CURSOR, name = "role_list_o", type = void.class)

edited Nov 06 '19 at 10:56

Jeff Bootsholz

2,971
15
70
141

answered Aug 08 '19 at 04:40

lê bá hoàng

1

1

Did it work for you? Because i have the same problem and this solution is not resolving the problem – stefano capra Nov 15 '19 at 14:23
I have the same problem you. I cannot call pakage directly in the oracle database. I have to create a new procedure to call pakage. Sorry for my English not good. Because I use translate. – lê bá hoàng Dec 25 '19 at 10:39

Reading ref cursor as output parameter in a stored procedure with spring data jpa returns null

3 Answers3