Unable to Read 2nd Refcursor in a Spring Boot application with Hibernate and Oracle 11g Database

user_1624880 0
Viewed 548

Problem Details

We are working on a Spring Boot application where in we are connecting to Stored Procs (all returning multiple refcursors) using @NamedStoredProcedureQuery for Oracle 11g DB. We are unable to read data from the second cursor at the same time. As soon as we provide the second result class for the 2nd refcursor we are getting an exception titled Invalid Column Name. Reading 1 cursor works fine.

Exception Details

javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: 
Error extracting results from CallableStatement Caused by: 
java.sql.SQLException: Invalid column name

Implementation Details

  • STORED PROCEDURE QUERY
@NamedStoredProcedureQuery (
   name = "getSP1Data",
   procedureName = "package_name",
   resultClasses = {Cursor1Response.class, Cursor2Response.class},
   parameters = {
      @StoredProcedureParameter(type = Integer.class, mode = ParameterMode.IN, name = "in_param_1"),
      @StoredProcedureParameter(type = void.class, mode = ParameterMode.REF_CURSOR, name = "cursor_1"),
      @StoredProcedureParameter(type = void.class, mode = ParameterMode.REF_CURSOR, name = "curosr_2"),
      @StoredProcedureParameter(type = String.class, mode = ParameterMode.INOUT, name = "in_out_param_2") }
)
  • CURSOR RESPONSE CLASSES
@Entity
public class Cursor1Response {

    @Id 
    @Column(name = "column_name_1") 
    private Date column1;

    @Column(name = "column_name_2") 
    private Double column2; 
}

@Entity 
public class Cursor2Response {

    @Id 
    @Column(name = "column_name_1") 
    private Date column1;

    @Column(name = "column_name_2") 
    private Double column2; 
}
  • DAO LAYER IMPLEMENTATION (FROM WHERE WE ACTUALLY CALL OUR STORED PROC QUERY)
StoredProcedureQuery query = entityManager.createNamedStoredProcedureQuery("getSP1Data");

// Code for setting all in params
query.getResultList(); // While execution of this line it is 
throwing the above mentioned exception

Has anyone worked on such scenario and have any ideas how to fix this exception?

cursor spring-boot oracle hibernate
1 Answers

I had the same issue. First I tried downgrading the Oracle JDBC driver from 8 to 6 with no results. After that I changed the annotation with a more programmatic stored procedure definition (note: avoid positional parameters, they cause the same problem...):

EntityManager entityManager = Persistence.createEntityManagerFactory("namehere").createEntityManager();
StoredProcedureQuery spq = 
    entityManager.createStoredProcedureQuery("YOUR.SP.NAME")
        .registerStoredProcedureParameter("name1", String.class, ParameterMode.IN)   
        .registerStoredProcedureParameter("name2", Integer.class, ParameterMode.OUT)
        .registerStoredProcedureParameter("name3", String.class, ParameterMode.OUT)
        .registerStoredProcedureParameter("name4", void.class,ParameterMode.REF_CURSOR)
        .setParameter(SP_PARAM_MSISDN, "paramval");
spq.execute();
user_100398750
Related