CODEWITHSUNDEEP
javaspringoracle-databasehibernate
ssp
ssp

Reputation: 19

How to Call Oracle Stored Procedure using Hibernate?

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??

Upvotes: 1

Views: 20745

Answers (2)

Vlad Mihalcea
Vlad Mihalcea

Reputation: 154120

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();

Upvotes: 2

Christopher Parker
Christopher Parker

Reputation: 4621

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

Upvotes: 1

Related Questions