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#4200780
Reply to the post :
http://www.jboss.com/index.html?module=bb&op=posting&mode=reply&p=4200780
_______________________________________________
jboss-user mailing list
jboss-user@lists.jboss.org
https://lists.jboss.org/mailman/listinfo/jboss-user
