How to Call Oracle Stored Procedure using Hibernate?

Question

Below is my Oracle stored procedure,

create or replace 
PROCEDURE "REPORT_HIBERNATE"(
start_year IN NUMBER,
output_data OUT SYS_REFCURSOR
)
AS 
BEGIN
DECLARE 
select_query LONG;

BEGIN
select_query :='SELECT 
  GLOBAL_ID,
  PROJECT_NUMBER,
  FISCAL_YEAR,
  FISCAL_MONTH,
  WEEK_END_DATE,
  ACTIVITY,
  COST_CENTER,
  ACTUAL_HOURS,
  APPROVED_HOURS,
  NORMALIZED_HOURS
FROM TS_TBTIMECARD_WEEKLY_DATA
where FISCAL_YEAR in
(SELECT  FISCAL_YEAR        
FROM TS_TBTIMECARD_WEEKLY_DATA        
where      FISCAL_YEAR               = ' ||start_year|| '
 )';
OPEN output_data FOR select_query;
END;
END REPORT_HIBERNATE;

Below is the Entity class:

@Entity
@SequenceGenerator(name="wkseq", initialValue=1, allocationSize=1)
@Table(name = "TS_TBTIMECARD_WEEKLY_DATA")



@NamedNativeQuery(name = "call_REPORT_HIBERNATE_procedure",
query = "{ CALL REPORT_HIBERNATE(:start_year) }", 
resultClass = TimecardWeeklyData.class, hints = {
@javax.persistence.QueryHint(name = "org.hibernate.callable", value = "true") })



public class TimecardWeeklyData {
...
}

DAOImpl:

Query query = sessionFactory.getCurrentSession().getNamedQuery("call_REPORT_HIBERNATE_procedure");

        query.setParameter("start_year",2014); 
    List result = query.list();

I get the following exception: Hibernate: { CALL REPORT_HIBERNATE(?) } org.hibernate.exception.GenericJDBCException: could not execute query ... and Caused by: java.sql.SQLException: Invalid column index

Plz let me know how will I call this stored procedure using Hibernate Annotation in Spring??

Vlad Mihalcea · Answer 1 · 2021-01-10T14:22:02.020

2

Considering you have a SYS_REFCURSOR OUT parameter:

CREATE OR REPLACE PROCEDURE post_comments ( 
   postId IN NUMBER, 
   postComments OUT SYS_REFCURSOR ) 
AS 
BEGIN
    OPEN postComments FOR
    SELECT *
    FROM post_comment 
    WHERE post_id = postId; 
END;

You can call the stored procedure like this:

StoredProcedureQuery query = entityManager
    .createStoredProcedureQuery("post_comments")
    .registerStoredProcedureParameter(1, Long.class, 
         ParameterMode.IN)
    .registerStoredProcedureParameter(2, Class.class, 
         ParameterMode.REF_CURSOR)
    .setParameter(1, 1L);
 
query.execute();
 
List<Object[]> postComments = query.getResultList();

edited Jan 10 '21 at 14:22

answered Nov 07 '16 at 05:36

Vlad Mihalcea

142,745
71
566
911

How to map the results from query.getResultList(); to customized class/Dto? – Shelly Jun 25 '20 at 17:01
I've a oracle store procedure to fetch data with cursor as a out parameter,```StoredProcedureQuery query = entityManager .createStoredProcedureQuery("getStudent").registerStoredProcedureParameter(1, Class.class, ParameterMode.REF_CURSOR) ``` I'm able to get results but not able to cast it to my student pojo class, stuck on this last step from past 1 day – Shelly Jun 25 '20 at 17:52

score 1 · Answer 2 · answered Apr 16 '14 at 13:25

Your OUT parameter isn't first. If you are able to modify your procedure, rearrange the parameters to make the OUT parameter the first parameter, then account for it in your @NamedNativeQuery annotation.

query = "{ CALL REPORT_HIBERNATE(?, :start_year) }",

How to Call Oracle Stored Procedure using Hibernate?

2 Answers2

Linked