call stored procedure in hibernate error

Question

stored procedure (spec only):

CREATE OR REPLACE PACKAGE "PACK_USER"     
as   
type contact is table of nvarchar2(50) INDEX BY BINARY_INTEGER;  
procedure create_user(  
user_id out number,  
pwd in nvarchar2,  
birthday in date,  
gender in number,  
address in nvarchar2,  
realName in nvarchar2,  
identity in nvarchar2,  
salary in float,  
contractTime in date,  
departmentId in number,  
positionId in number 
);  
end pack_user;
/

It is OK when I call it from toad. But When I call it from hibernate, it said "invalid SQL statement", but no cause displayed. hibernate mapping:

<sql-query name="create_user" callable="true">
    <return-scalar column="user_id" type="integer"/>
    { call PACK_USER.CREATE_USER(?,:pwd,:birthday,:gender,:address,:realName,:identity,:salary,:contractTime,:departmentId,:positionId)}
</sql-query>

called by:

Session session = this.getSession();
Query q = session.getNamedQuery("create_user");
q.setString("pwd", userInfo.getIndentity());
q.setDate("birthday", userInfo.getBirthday());
q.setInteger("gender", userInfo.isGender() ? 1 : 0);
q.setString("address", userInfo.getHomeAddress());
q.setString("realName", userInfo.getRealname());
q.setString("identity", userInfo.getIndentity());
q.setFloat("salary", userInfo.getBaseSalary());
q.setDate("contractTime", userInfo.getContractTime());
q.setInteger("departmentId", userInfo.getDepartmentId());
q.setInteger("positionId", userInfo.getPositionId());
int res = (Integer)q.uniqueResult();
return res;

PS: As this is a course project, oracle and stored procedure is required while hibernate is not.
I have already tried to move the procedure out of the package, or create a function instead of the procedure. Neither works.
oracle version 10g

Answer 1

There might be some problem in your configuration file, look below example for more clarification :

CREATE OR REPLACE PROCEDURE SP_LIB_DTL(p_cursor    out sys_refcursor,
                                       in_brnch_cd in number,
                                       in_auth_cd in number)
as
  bookName varchar2(8);
  ISBN     number;
begin
  bookName := null;
  ISBN     := 0;
  open p_cursor for
    select l.book_name, l.isbn_nbr
      into bookName, ISBN
      from LIB_BRNCH_DTL l
     where l.branch_code = in_brnch_cd
     and l.auth_code = in_auth_cd;

end;

Hibernate xml

<?xml version=”1.0″ encoding=”utf-8″?>
<!DOCTYPE hibernate-mapping PUBLIC “-//Hibernate/Hibernate Mapping DTD 3.0//EN”
“http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd“>
<hibernate-mapping>
<class name=”com.org.lib.LibraryDetails”>
<id name=”ISBN” type=”long” />
<property name=”bookName” type=”string” />
</class>
 <sql-query name=”LIB_SP” callable=”true”>
 <return class=”com.org.lib.LibraryDetails”>
   <return-property name=”ISBN” column=”isbn_nbr” />
   <return-property name=”bookName” column=”book_name” />
 </return>
  {  call SP_LIB_DTL(? , :branchCD ,:authorCD) }
 </sql-query>
</hibernate-mapping>

Make sure you have used the correct database field name value for the column attribute for the return property mapping. You will get the following error if you don’t map the correct databse field name

could not execute query; bad SQL grammar [{ call SP_LIB_DTL(? , ?) }]; nested exception is java.sql.SQLException: Invalid column name

Here is the DAO implementation for executing query and to set the bind parameter values.

public  List selectBooks(final BigDecimal branchCode,final BigDecimal authorCode){
        return (List) getHibernateTemplate().execute(new HibernateCallback() {
              public Object doInHibernate(Session session) throws HibernateException, SQLException
              {
                  Query q = session.getNamedQuery(“LIB_SP”);
                  q.setLong(“branchCD”, branchCode.longValue());
                  q.setLong(“authorCD”, authorCode.longValue());
                  return q.list();
              }
          });
      }