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.