3 Replies Latest reply: Oct 17, 2013 2:16 AM by Durgesh Kumar RSS

    how to call oracle stored procedure with hibernate

      Hi all,
      I wrote a stored procedure below:

      create or replace
      PROCEDURE SP_GET_NUMBER (p_cursor out sys_refcursor, departmentId in number, userId in number, documentTypeId in number)
      regId integer;
      sqlScript varchar2(60);
      sqlScript:='select registrationnumber_seq.nextval from dual';
      execute immediate sqlScript into regid;
      dbms_output.put_line('Nextval is: '||TO_CHAR(regid));
      insert into roketsanuser.registrationnumbers
      (id, departmentid, documenttypeid, registrationnumber, registrationstatus, status, updatedate, updateuserid, version)
      (regId, null, null, regid, 0, 0, sysdate, 0, 0);
      open p_cursor for
      select id
      from roketsanuser.registrationnumbers reg
      where reg.id = regid;

      And i define that within annotations in my entity class:

      @Entity(name = "RegistrationNumber")
      @Table(name = "REGISTRATIONNUMBERS")
      @SequenceGenerator(name = "REGISTRATIONNUMBER_SEQ", sequenceName = "REGISTRATIONNUMBER_SEQ")
      @org.hibernate.annotations.NamedNativeQuery(name = "SP_GET_NUMBER", query = "{call ?= SP_GET_NUMBER(:departmentId, :userId, :documentTypeId)}",
      callable = true, readOnly = true, resultClass = RegistrationNumber.class)
      @EntityListeners( { BaseEntityListener.class })
      public class RegistrationNumber extends BaseEntity

      i call query in my Dao class:

      public RegistrationNumber getNumberForIncomingPaperworkByStoredProcedure(EntityManager manager, RegistrationNumber registrationNumber)
      Session session = (Session)manager.getDelegate();
      org.hibernate.impl.SQLQueryImpl query = (SQLQueryImpl)session.getNamedQuery("SP_GET_NUMBER");

      query.setParameter("departmentId", ObjectUtil.isNotNull(registrationNumber.getDepartment()) ? registrationNumber.getDepartment().getId() : "");
      query.setParameter("userId", ObjectUtil.isNotNull(registrationNumber.getUser()) ? registrationNumber.getUser().getId() : "");
      query.setParameter("documentTypeId", ObjectUtil.isNotNull(registrationNumber.getDocumentType()) ? registrationNumber.getDocumentType().getId() : "");
      Collection list=query.list();
      return new RegistrationNumber();

      But when i execute query occured exception:
      <02-01-2013 10:04:24 o'clock EET> <Notice> <Stdout> <BEA-000000> <Hibernate:
      /* named native SQL query SP_GET_NUMBER */ {call ?= SP_GET_NUMBER(?, ?, ?)}>
      <02-01-2013 10:04:27 o'clock EET> <Notice> <Stdout> <BEA-000000> <10:04:27,811 ERROR [IncomingPaperworkMBean] EJB Exception: ; nested exception is:
           org.hibernate.exception.SQLGrammarException: could not execute query; nested exception is: org.hibernate.exception.SQLGrammarException: could not execute query>

      That might be the reason, please help me?

      Thanks in advance