Invoking a ORACLE Stored Procedure using hibernate:
There was requirement where we had to select and update a given column or row in one atomic operation and return the results of the updated row. So Oracle has support for this with the RETURNING INTO CLAUSE.
Eg: Example I wanted to update check number and here is the statement to select and update at once.
There was requirement where we had to select and update a given column or row in one atomic operation and return the results of the updated row. So Oracle has support for this with the RETURNING INTO CLAUSE.
Eg: Example I wanted to update check number and here is the statement to select and update at once.
UPDATE MY_BANK_ACCOUNT SET LAST_CHECK_NO = LAST_CHECK_NO+1, UPDATED_DT = SYSDATE WHERE INSTITUTION_BANK_ID= 101 RETURNING LAST_CHECK_NO INTO v_next_check_number;
where v_next_check_number is variable.
Table : MY_BANK_ACCOUNT used in this example
-------------------------------------------
So here is the Stored procedure to update and return the Row:INSTITUTION_BANK_ID NOT NULL NUMBER INSTITUTION_ID NOT NULL NUMBER ROUTING_NUMBER NOT NULL VARCHAR2(50) ACCOUNT_NUMBER NOT NULL VARCHAR2(50) LAST_CHECK_NO NOT NULL NUMBER FRACTION_CODE NOT NULL VARCHAR2(50) ACTIVE_STATUS_IND NOT NULL VARCHAR2(1) CREATED_BY NOT NULL VARCHAR2(50) CREATED_DT NOT NULL DATE UPDATED_BY VARCHAR2(50) UPDATED_DT DATE
CREATE OR REPLACE PROCEDURE PROC_GET_NEW_CHECK_NUMBER( p_cursor out sys_refcursor,p_bankid IN NUMBER, p_application_name IN VARCHAR ) AS v_next_check_number NUMBER; BEGIN UPDATE MY_BANK_ACCOUNT SET LAST_CHECK_NO = LAST_CHECK_NO+1, UPDATED_DT = SYSDATE, UPDATED_BY = p_application_name WHERE INSTITUTION_BANK_ID= p_bankid RETURNING LAST_CHECK_NO INTO v_next_check_number; --COMMIT; Dont commit here as this stored proc is used in Global Txn so -- we get error which says COMMIT is not allowed in a subordinate session -- In case you are not using Global Txn (Like I do in J2ee) use commit here. OPEN p_cursor for SELECT INSTITUTION_BANK_ID, INSTITUTION_ID, ROUTING_NUMBER, ACCOUNT_NUMBER, /* copy the value from local variable that we stored during update */ v_next_check_number AS LAST_CHECK_NO, FRACTION_CODE, ACTIVE_STATUS_IND, CREATED_BY, CREATED_DT, UPDATED_BY, UPDATED_DT FROM MY_BANK_ACCOUNT WHERE INSTITUTION_BANK_ID=p_bankid; END PROC_GET_NEW_CHECK_NUMBER ; /NOTE: Hibernate expects the first argument always to be the Cursor that you will be returning. IT HAS TO BE THE FIRST ARGUMENT. I uses SYS Ref cursor you can use re cursor as well.
In case you want to test your stored procedure in SQLPLUS use the below code.
SET SERVEROUTPUT ON; DECLARE v_cur SYS_REFCURSOR; v_inst_bank_id NUMBER; v_institute_id NUMBER; v_routing_number VARCHAR2(50); v_account_number VARCHAR2(50); v_next_check_number NUMBER; v_fraction_code VARCHAR2(50); v_active_status_ind VARCHAR2(1); v_created_by VARCHAR2(50); v_created_dt DATE; v_updated_by VARCHAR2(50); v_updated_dt DATE; BEGIN -- HERE THE BANK ID WE ARE USING IS 1. CHANGE IT ANY VALUE YOU WANT. PROC_GET_NEW_CHECK_NUMBER(v_cur,1,'MytestAPP'); COMMIT; -- Caller must commit else you will keep locking that row. -- (Since my stored doesn't commit as I am using it in Global TXN. If you have commited in side stored proc ignore this commit ) LOOP FETCH v_cur INTO v_inst_bank_id,v_institute_id,v_routing_number,v_account_number, v_next_check_number,v_fraction_code,v_active_status_ind, v_created_by,v_created_dt,v_updated_by,v_updated_dt; EXIT WHEN v_cur%NOTFOUND; dbms_output.put_line( 'New Check Number =' || v_next_check_number ||' For Bank Id='|| v_inst_bank_id); dbms_output.put_line( 'Routing number='|| v_routing_number ||' Account num='|| v_account_number); END LOOP; CLOSE v_cur; END;
Now lets Map this Stored procedure to Java class in HBM file.
MyBankAccount.hbm.xml:
<?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> <class name="com.test.reddy.dao.model.MyBankAccount" table="MY_BANK_ACCOUNT"> <id name="institutionBankId" type="java.lang.Long"> <column name="INSTITUTION_BANK_ID" /> </id> <property name="institutionId" type="java.lang.Long"> <column name="INSTITUTION_ID" /> </property> <property name="routingNumber" type="string"> <column name="ROUTING_NUMBER" /> </property> <property name="accountNumber" type="string"> <column name="ACCOUNT_NUMBER" /> </property> <property name="lastCheckNumber" type="java.lang.Long"> <column name="LAST_CHECK_NO" /> </property> <property name="fractionCode" type="string"> <column name="FRACTION_CODE" /> </property> <property name="activeStatusInd" type="string"> <column name="ACTIVE_STATUS_IND" /> </property> <property name="createdBy" type="string"> <column name="CREATED_BY" /> </property> <property name="createdDT" type="java.util.Date"> <column name="CREATED_DT" /> </property> <property name="updatedBy" type="string"> <column name="UPDATED_BY" /> </property> <property name="updatedDT" type="java.util.Date"> <column name="UPDATED_DT" /> </property> </class> <!-- The first argument of the stored procedure is actually an OUT parameter from Oracle. So map how the cursor will be interpreted by hibernate. Dont mess here. --> <sql-query name="MyBankAccount.getNextCheckNumber" callable="true"> <return class="com.test.reddy.dao.model.MyBankAccount"> <return-property name="institutionBankId" column="INSTITUTION_BANK_ID" /> <return-property name="institutionId" column="INSTITUTION_ID" /> <return-property name="routingNumber" column="ROUTING_NUMBER" /> <return-property name="accountNumber" column="ACCOUNT_NUMBER" /> <return-property name="lastCheckNumber" column="LAST_CHECK_NO" /> <return-property name="fractionCode" column="FRACTION_CODE" /> <return-property name="activeStatusInd" column="ACTIVE_STATUS_IND" /> <return-property name="createdBy" column="CREATED_BY" /> <return-property name="createdDT" column="CREATED_DT" /> <return-property name="updatedBy" column="UPDATED_BY" /> <return-property name="updatedDT" column="UPDATED_DT" /> </return> { call PROC_GET_NEW_CHECK_NUMBER(?,:bankId,:appName) } </sql-query> </hibernate-mapping>
Please create a MyBankAccount.java file with above properties
package com.test.reddy.dao.model;
public class MyBankAccount implements Serializable { private Long institutionBankId; private Long institutionId; private String routingNumber; private String accountNumber; private Long lastCheckNumber; private String fractionCode; private String activeStatusInd; private String createdBy; private Date createdDT; private String updatedBy; private Date updatedDT; //PLEASE GENERATE GETTERS and SETTERS - Left it intentionally though you need add getter and setters. }Calling your Stored procedure now using Java code:
public static void main(String[] args)throws Exception
System.out.println("Calling test cursor");
// Please get hibernate session
Session session = HibernateUtil.getSession();
//Clear any previously loaded Stuff always Reload fresh from DB.
session.clear();
//Its just another SQL.
Query qry = session.getNamedQuery("MyBankAccount.getNextCheckNumber");
qry.setLong("bankId",1);
qry.setString("appName","MyTestApp");
Object result = qry.uniqueResult();
System.out.println(result);
System.out.println(((MyBankAccount)result).getLastCheckNumber());
}
PROBLEMS I FACED:
We upgraded from Oracle9 to Oracle 11.2.0.1.0 . The OracleTypes class in 11g has been moved (I guess after Oracle 9) to a new package from "oracle.jdbc.driver.OracleTypes" to "oracle.jdbc.OracleTypes" .
So I was getting the below error
- Caused by: org.springframework.orm.hibernate3.HibernateSystemException: Problem while trying to load or access OracleTypes.CURSOR value; nested exception is org.hibernate.HibernateException: Problem while trying to load or access OracleTypes.CURSOR value.
org.springframework.orm.hibernate3.HibernateAccessor.convertHibernateAccessException(HibernateAccessor.java:412)
So I had to upgrade from Hibernate 3.1.jar to hibernate-core-3.3.2.GA.jar. This may also require
- javassist-3.9.0.GA.jar , slf4j-api-1.6.4.jar and slf4j-log4j12-1.6.4.jar
Inside hibernate-core-3.3.2.GA.jar source code they check first for
ORACLE_TYPES_CLASS_NAME = "oracle.jdbc.OracleTypes";
if not found then they look for the older version.
DEPRECATED_ORACLE_TYPES_CLASS_NAME = "oracle.jdbc.driver.OracleTypes";
(You don't need to worry about this. We can trust Gavin King the Guru of Hibernate , just use hibernate-core-3.3.2.GA.jar)
Also note that hibernate Dialect Should be set to
hibernate.dialect ===> org.hibernate.dialect.Oracle10gDialect
or org.hibernate.dialect.Oracle9iDialect or org.hibernate.dialect.Oracle8iDialect in hibernate.cfg.xml or corresponding spring xml file which ever approach you are using.
Please do not use org.hibernate.dialect.OracleDialect and org.hibernate.dialect.Oracle9Dialect (no i ) - its deprecated.
- Then on server start up the application started throwing XML Parsing exceptions.
org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1420)
org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:519)
org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:456)
After several hours of removing code and adding as few hbm files to the application I found that a common hbm file that had few properties defined in another file was causing this.
My hibernate File had some thing like
<!ENTITY CommonProperties SYSTEM "file://hibernate-mapping/CommonProperties.hbm.xml">
With hibernate-core-3.3.2.GA.jar this doesn't work. So change the file to classpath as shown below.
<!ENTITY CommonProperties SYSTEM "classpath://hibernate-mapping/CommonProperties.hbm.xml">
- I was using a older JDBC driver so after fixing the above error I was getting
at com.ibm.ejs.jms.listener.ServerSessionDispatcher.dispatch(ServerSessionDispatcher.java:44)
at com.ibm.ejs.container.MDBWrapper.onMessage(MDBWrapper.java:100)
java.sql.SQLSyntaxErrorException: ORA-00900: invalid SQL statement
To fix this I upgraded my JDBC driver to the correct version that matches the Oracle installation. So in my case I used ojdbc6_11g_11_2_0_1.jar since my Oracle instance :Oracle 11.2.0.1.0
- Finally when I added the above as part of an EJB bean which is container managed it started throwing errors
org.hibernate.transaction.JTATransactionFactory.getUserTransaction(JTATransactionFactory.java:173)
org.hibernate.transaction.JTATransactionFactory.createTransaction(JTATransactionFactory.java:149)Thanks to this Article
http://www.ibm.com/developerworks/websphere/techjournal/0609_alcott/0609_alcott.html
If its EJB and is using Container manager transaction then use
<prop key="hibernate.transaction.factory_class">org.hibernate.transaction.CMTTransactionFactory</prop>
DO NOT USE
<prop key="hibernate.transaction.factory_class">org.hibernate.transaction.JTATransactionFactory</prop>
its for BMT - Bean manager Transaction . Also when you use BMT Hiberenate expects this in hibernat configuration file.
<prop key="jta.UserTransaction">java:comp/UserTransaction</prop>And finally the stored procedure worked.
Well I added all the errors that I got and a fix to it as there were not too many threads on few topics and also I was trying to upgrade hibernate and oracle at same time so thought it would help some one out there.
