Calling Oracle Stored Procedure from Hibernate
March 3rd, 2009 | 
Author: 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();
}
});
}
Posted in 
Tags:
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?
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..
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.
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.
And what about a stored procedure that returns nothing? My stored proc has only input parameters. How can i confiugure and call it?
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.
Pino,
Try to define a REF CURSOR and return the number of rows affected by the insert/update/delete operation
Hope this helps..
Hi,
Thanks for the nice post.
I am trying to call a stored function from Hibernate.
The function:
create or replace
FUNCTION FCT_CRYPTE_PWD(input_string IN VARCHAR2) RETURN VARCHAR2
IS
hex_digest varchar2(32);
digest varchar2(16);
BEGIN
digest := DBMS_OBFUSCATION_TOOLKIT.MD5(INPUT_STRING => input_string);
SELECT Lower(RAWTOHEX(digest)) INTO hex_digest FROM dual;
RETURN hex_digest;
END;
Is it possible to do so?
If Yes, How should the mapping and acces logic be?
im getting this error while executing store procedure through hibernate :
org.hibernate.HibernateException: Errors in named queries: CHECKUSERID1
{ ? = call CHECKUSERID1(?) }
—————————————————————–
package component;
import org.hibernate.*;
import org.hibernate.cfg.*;
import java.util.List;
public class ProcedureCall
{
public static void main(String args[])
{
Session session = null;
SessionFactory factory = null;
Transaction t = null;
try
{
factory = new Configuration().configure().buildSessionFactory();
session = factory.openSession();
Query query = session.getNamedQuery(“CHECKUSERID1″);
List results = query.list();
session.close();
}
catch(Exception e)
{
System.out.println(e.toString());
}
}
}
———————————————————–
CREATE OR REPLACE PROCEDURE CHECKUSERID1
(
ref_cursor OUT SYS_REFCURSOR
)
AS
IC_USER_TYPE varchar2(8);
BEGIN
OPEN ref_cursor FOR
SELECT IC_USER_TYPE into IC_USER_TYPE from USER_PROFILE
where trim(IC_USER_ID)=’SS1000′;
END CHECKUSERID1;
/
sorry didnt mention the hbm file
SQE_TABLE_CONTACT
{ ? = call CHECKUSERID1(?) }
{ ? = call CHECKUSERID1(?) }
And how in that case to act?
I am new to hibernate and java.
I am doing a sample application to call the SP from Hibernate. I get the List but how do I see the values contained in the list. when I iterare the List ..it gives me the object but not the actual value.
Mapping:
{ call SP_LIB_DTL(? , :branchCD ,:authorCD) }
============
Code to invoke the SP
Query q = session.getNamedQuery(“LIB_SP”);
q.setLong(“branchCD”, lib.getBranch_Code());
q.setLong(“authorCD”, lib.getAuth_Code());
//@SuppressWarnings(“unchecked”)
System.out.println(“before getting the list”);
List list= q.list();
System.out.println(“size of the list is ” + list.size());
for(int i=0; i<list.size();i++){
System.out.println("list get"+list.get(i));
}
System.out.println("after list");
==========================
results:
Hibernate: { call SP_LIB_DTL(? , ? ,?) }
size of the list is 1
list getcom.fidelity.LibraryDetails@f0c85e
after list
Appreciate your help
Puru