[jboss-user] [EJB/JBoss] - How to call oracle function from ejb3

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. View the original post : http://www.jboss.com/index.html?module=bb&op=viewtopic&p=4200780#... Reply to the post : http://www.jboss.com/index.html?module=bb&op=posting&mode=reply&a...