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

zzuli do-not-reply at jboss.com
Sat Jan 10 09:52:51 EST 2009


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




More information about the jboss-user mailing list