Thursday, March 15, 2012

Calling Stored procedure using hibernate.

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.
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
-------------------------------------------
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 
So here is the Stored procedure to update and return the Row:

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.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:679)
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.
 caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'ORADataSource' defined in class path resource [spring-config/sample-dao-application-context.xml]: Invocation of init method failed; nested exception is org.springframework.beans.factory.support.BeanDefinitionValidationException: Couldn't find an init method named 'JndiObjectFactoryBean' on bean with name 'ORADataSource'
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
 org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute query; SQL [{ call PROC_GET_NEW_CHECK_NUMBER(?,?,?) }]; nested exception is org.hibernate.exception.SQLGrammarException: could not execute query
  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 
Exception: org.hibernate.TransactionException: Could not find UserTransaction in JNDI [java:comp/UserTransaction]
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.