0 Replies Latest reply on Jan 10, 2009 9:52 AM by zwz mycoy

    How to call oracle function from ejb3

    zwz mycoy Newbie

      Hi,everyone:
      i'm trying to call an oracle query-function returning a result-set 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:
      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

      i have tried several other ways of writing query:
      " createNativeQuery("{ ?=call getSecThreadCount(?,?) }") " //hibernate using this way
      " createNativeQuery("select getSecThreadCount(?,?) from dual") "
      but all failed.



      i have successfully called the function from hibernate.

      and i have successfully called a mysql query-stored-procedure which returns a result-set using the

      same code; also it's ok to call an oracle function which returns an int using the code

      " entityManager.createNativeQuery("SELECT sum_total(?1) FROM DUAL") " from ejb3.
      so, i believe it's totally possible to get the result-set of oracle function or SP using ejb3.

      but i cannot figured out the right way.
      i use oracle11g, jboss5GA.
      could anyone help me? thanks a lot.