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 · Accepted Answer · 2021-01-10 14:22:02Z

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, 2021 at 14:22

answered Nov 7, 2016 at 5:36

Vlad Mihalcea

156k85 gold badges599 silver badges984 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Shelly Over a year ago

How to map the results from query.getResultList(); to customized class/Dto?

Shelly Over a year ago

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

Christopher Parker · Accepted Answer · 2014-04-16 13:25:31Z

1

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) }",

answered Apr 16, 2014 at 13:25

Christopher Parker

4,6412 gold badges31 silver badges33 bronze badges

Collectives™ on Stack Overflow

How to Call Oracle Stored Procedure using Hibernate?

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related