Calling an Oracle Stored Procedure from Hibernate
After looking at several posts on the Internet that had various degrees of correctness we finally got this to work. So I thought it would be good to share. In the specific case we were using a stored procedure to access a sequence number (yes, it is outside of Hibernate’s cachable domain).
First, we place the stored procedure definitions inside of a new mapping file:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
<sql-query name="getNextSequence" callable="true">
<return-scalar column="NextVal" type="integer"/>
{ call get_next_sequence(?,:tableName) }
</sql-query>
</hibernate-mapping>
Notes:
- the first argument of the stored procedure is actually an OUT parameter from Oracle.
- the actual name of the output ‘column’ must be specified in the return-scalar field
Then the Java code becomes pretty standard:
Session session = TestHibernateSessionFactory.getSession();
Query query = session.getNamedQuery("getNextSequence");
query.setString("tableName", "Table_X");
Object result = query.uniqueResult();
System.out.print(result);