Calling Oracle Stored Procedure from Hibernate

Tagged with: Databases & Architecture, Software Development, Technical Tips & FAQs

While calling Oracle stored procedure from Hibernate, we need to follow some rules. There can be only one return value, and this must be a reference cursor. This return reference cursor must be the first and only out value for the stored procedure. It is required that the stored procedure return a reference cursor to be used from Hibernate.

Here is an example of the stored procedure which produces the book name and ISBN number for a given branch and author:

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 mapping for return class and named query: 

<?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 right database 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 the query and setting 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(); 
             } 
         }); 
     } 

Need help with your data management? Contact us today.