Calling Oracle Stored Procedure from Hibernate
General Technology, Java, Oracle · Tags: Java, Oracle
While calling Oracle stored procedure from Hibernate there is couple of rules that we need to follow. There can be only one return value and this must be a reference cursor. This return reference cusor must be the first and only out value for the stored procedure. It is required that stored procedure must return a reference cursor to be used from Hibernate.
Here is my stored procedure which return book name and ISBN number for a given branch and for a given 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 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();
             }
         });
     }
June 18th, 2009 9:35 pm
Thank s for this post. I have another doubt. If i want to return a string frm the procedure how should i configure that in the hbm file?
also how should i call in the java?
June 19th, 2009 8:14 am
You need to open a cursor and return the cursor. Curosr can have String return out parameter in it. In the given example cursor has ‘bookName’ out parameter which is mapped to a property in the LibraryDetails class in the hibernate mapping. When you call the stored procedure you get a list of LibraryDetails object. Just need to iterate through the list and get LibraryDetails object values out of it. Hope this helps..
July 9th, 2009 2:28 pm
I did call a stored procedure returning a cursor, but i can’t call a stored function returning a number for exemple.
How can i do that?
My hibernate maping:
{ ? = call PA_TIPO_LOCAL.INC_TIPO_LOCAL() }
My java code:
Query q = ManageDAO.getSession().getNamedQuery(”incTipoLocal”);
q.uniqueResult();
The Error:
Hibernate:
{ ? = call PA_TIPO_LOCAL.INC_TIPO_LOCAL() }
16:27:31,546 WARN JDBCExceptionReporter:100 – SQL Error: 6550, SQLState: 65000
16:27:31,562 ERROR JDBCExceptionReporter:101 – ORA-06550: line 1, column 13:
PLS-00382: expression is of wrong type
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
Exception in thread “main” org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:90)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
at org.hibernate.loader.Loader.doList(Loader.java:2231)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2125)
at org.hibernate.loader.Loader.list(Loader.java:2120)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:312)
at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1722)
at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:165)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:175)
at org.hibernate.impl.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:835)
at br.es.csi.cad.dao.TipoLocalDAO.incTipoLocal(TipoLocalDAO.java:30)
at br.es.csi.cad.dao.TipoLocalDAO.main(TipoLocalDAO.java:38)
Caused by: java.sql.SQLException: ORA-06550: line 1, column 13:
PLS-00382: expression is of wrong type
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
Can you help me, please?
Thanks.
July 9th, 2009 5:12 pm
Per the Hibernate Reference doc (http://docs.jboss.org/hibernate/stable/core/reference/en/html_single/#sp_query)
For Oracle the following rules apply:
*A function must return a result set. The first parameter of a procedure must be an OUT that returns a result set. This is done by using a SYS_REFCURSOR type in Oracle 9 or 10. In Oracle you need to define a REF CURSOR type. See Oracle literature for further information.
October 23rd, 2009 5:21 am
And what about a stored procedure that returns nothing? My stored proc has only input parameters. How can i confiugure and call it?
November 10th, 2009 1:12 pm
calling storeprocedure which returns nothing.
{ call P_TEST.SP_UPDATE_TEST_USER(:id,:displayName) }
———————————————————————————-
procedure sp_update_test_user(intId in integer, vchDisplayName in varchar2)is
begin
update test_user tu set tu.display_name=vchDisplayName where tu.id=intId;
end sp_update_test_user;
——————————————————————————–
public boolean updateUserDB(){
Transaction tx = null;
Session session = HibernateUtil.getInstance().getCurrentSession();
try {
System.out.println(”********** UpdateUserDB *************”);
System.out.println(”id –> “+paramBean.getId());
System.out.println(”DisplayName –> “+paramBean.getDisplayName());
System.out.println(”*************************************”);
tx = session.beginTransaction();
Query query = session.getNamedQuery(”SP_UPDATE_TEST_USER”);
query.setInteger(”id”, Integer.parseInt(”"+paramBean.getId()));
query.setString(”displayName”, paramBean.getDisplayName());
int retInt=query.executeUpdate();
tx.commit();
return true;
} catch (RuntimeException e) {
if (tx != null && tx.isActive()) {
try {
// Second try catch as the rollback could fail as well
tx.rollback();
} catch (HibernateException e1) {
logger.debug(”Error rolling back transaction”);
}
// throw again the first exception
throw e;
}
return false;
}
}
———————————————————————————–
I hope this will help you.
November 10th, 2009 1:52 pm
Pino,
Try to define a REF CURSOR and return the number of rows affected by the insert/update/delete operation
Hope this helps..