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

[zzuli]

   Hello, everyone.
   i'm trying to call an oracle query-function 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:
    1. 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

    2. the calling code:
@SqlResultSetMapping(name = "getSecThreadCount_Mapping")
@NamedNativeQuery(name = "getSecThreadCount",
query = "{?=call getSecThreadCount(:secId,:avai)}",
resultSetMapping = "getSecThreadCount_Mapping",
hints = {@QueryHint(name = "org.hibernate.callable", value = "true"),
@QueryHint(name = "org.hibernate.readOnly", value = "true")})

public Object getSectionThreadCount(int secId,int avai){
    Query query=manager.createNamedQuery("getSecThreadCount"); 
    query.setParameter("secId", secId);
    query.setParameter("avai", avai);

    return query.getSingleResult();
}
    but i run into 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: lost in index IN or OUT parameter:: 3

    By the way, i have successfully called the function from hibernate. And i use oracle 11g, JBoss5 RC1.

    Could anyone tell me how to call the function from EJB3?
    Thanks.


View the original post : http://www.jboss.com/index.html?module=bb&op=viewtopic&p=4181639#4181639

Reply to the post : http://www.jboss.com/index.html?module=bb&op=posting&mode=reply&p=4181639