0 Replies Latest reply on Oct 11, 2008 5:13 AM by zwz mycoy

    How to call oracle function from ejb3

    zwz mycoy Newbie

      Hello, everyone.
      i'm trying to call an oracle query-function from ejb3.

      The oracle function:
      create or replace FUNCTION getSecThreadCount(secId in NUMBER,avai in NUMBER)
      RETURN SYS_REFCURSOR is cur SYS_REFCURSOR;
      m_sql VARCHAR2(250);
      BEGIN
      m_sql:='select count(thrId) from thread where secId='|| secid||'
      and thrAvai='|| avai;
      open cur for m_sql;
      return cur;
      END;

      I'v tried several ways to call it,but all failed:
      1. the calling code:
      public Object getSectionThreadCount(int secId,int avai){
      Query query=manager.createNativeQuery("{call getSecThreadCount (?,?) }");
      query.setParameter(1, secId);
      query.setParameter(2, avai);

      return query.getSingleResult();
      }
      but i got the exception:
      Exception in thread "main" javax.ejb.EJBException: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query; nested exception is: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query
      javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query
      ....
      Caused by: java.sql.SQLException: ORA-06550: row 1, col 7:
      PLS-00221: 'GETSECTHREADCOUNT' not procedure or not defined
      ORA-06550: row 1, col 7:
      PL/SQL: Statement ignored

      2. the calling code:
      @SqlResultSetMapping(name = "getSecThreadCount_Mapping")
      @NamedNativeQuery(name = "getSecThreadCount",
      query = "{?=call getSecThreadCount(:secId,:avai)}",
      resultSetMapping = "getSecThreadCount_Mapping",
      hints = {@QueryHint(name = "org.hibernate.callable", value = "true"),
      @QueryHint(name = "org.hibernate.readOnly", value = "true")})

      public Object getSectionThreadCount(int secId,int avai){
      Query query=manager.createNamedQuery("getSecThreadCount");
      query.setParameter("secId", secId);
      query.setParameter("avai", avai);

      return query.getSingleResult();
      }
      but i run into the exception:
      Exception in thread "main" javax.ejb.EJBException: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query; nested exception is: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query
      javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query
      .....
      Caused by: java.sql.SQLException: lost in index IN or OUT parameter:: 3

      By the way, i have successfully called the function from hibernate. And i use oracle 11g, JBoss5 RC1.

      Could anyone tell me how to call the function from EJB3?
      Thanks.