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.
This comment has been removed by the author.
ReplyDeleteThank you very much! It really helped me.
ReplyDelete