Atomikos Forum

I have a simple Hibernate application running successfully against Oracle 10G2 using Spring 2.5.6 and the Hibernate 3.5.6 transaction manager. When I run the same application using the Atomikos Essentials 3.7.0M5 JTA transaction manager, the app fails due to: java.sql.BatchUpdateException: ORA-00942: table or view does not exist

Here is a console log using the Hibernate txn manager:
Hibernate: select ASSOCIATIONPOC.SQ_RQST.nextval from dual
Hibernate: select ASSOCIATIONPOC.SQ_DX.nextval from dual
Hibernate: select ASSOCIATIONPOC.SQ_DX.nextval from dual
Hibernate: select ASSOCIATIONPOC.SQ_DX.nextval from dual
Hibernate: insert into ASSOCIATIONPOC.RQST (RQST_ID, START_DATE, END_DATE, CLAIM_NUMBER, RQST_PID) values (?, ?, ?, ?, ?)
Hibernate: insert into ASSOCIATIONPOC.DX (DX_CODE, DX_DESC, DX_PID) values (?, ?, ?)
Hibernate: insert into ASSOCIATIONPOC.DX (DX_CODE, DX_DESC, DX_PID) values (?, ?, ?)
Hibernate: insert into ASSOCIATIONPOC.DX (DX_CODE, DX_DESC, DX_PID) values (?, ?, ?)
Hibernate: insert into ASSOCIATIONPOC.RQST_DX (RQST_PID, DX_PID) values (?, ?)
Hibernate: insert into ASSOCIATIONPOC.RQST_DX (RQST_PID, DX_PID) values (?, ?)
Hibernate: insert into ASSOCIATIONPOC.RQST_DX (RQST_PID, DX_PID) values (?, ?)
*** Nonentity association: 1 non-XA create transactions took 0.218 seconds.
Hibernate: select request0_.RQST_PID as RQST1_1_, request0_.RQST_ID as RQST2_1_, request0_.START_DATE as START3_1_, request0_.END_DATE as END4_1_, request0_.CLAIM_NUMBER as CLAIM5_1_ from ASSOCIATIONPOC.RQST request0_ where request0_.RQST_PID=?
*** Nonentity association: 1 non-XA retrievals took 0.109 seconds.
Hibernate: DELETE FROM ASSOCIATIONPOC.RQST
Hibernate: DELETE FROM ASSOCIATIONPOC.RQST_DX
Hibernate: DELETE FROM ASSOCIATIONPOC.DX
Hibernate: DELETE FROM ASSOCIATIONPOC.RQST_WITH_PID
Hibernate: DELETE FROM ASSOCIATIONPOC.RQST_DX_WITH_PID

Here is a console log showing the XA output:
Hibernate: select ASSOCIATIONPOC.SQ_RQST.nextval from dual
Hibernate: select ASSOCIATIONPOC.SQ_DX.nextval from dual
Hibernate: select ASSOCIATIONPOC.SQ_DX.nextval from dual
Hibernate: select ASSOCIATIONPOC.SQ_DX.nextval from dual
Hibernate: insert into ASSOCIATIONPOC.RQST (RQST_ID, START_DATE, END_DATE, CLAIM_NUMBER, RQST_PID) values (?, ?, ?, ?, ?)
Hibernate: insert into ASSOCIATIONPOC.DX (DX_CODE, DX_DESC, DX_PID) values (?, ?, ?)
Hibernate: insert into ASSOCIATIONPOC.DX (DX_CODE, DX_DESC, DX_PID) values (?, ?, ?)
Hibernate: insert into ASSOCIATIONPOC.DX (DX_CODE, DX_DESC, DX_PID) values (?, ?, ?)
Hibernate: insert into ASSOCIATIONPOC.RQST_DX (RQST_PID, DX_PID) values (?, ?)
Hibernate: insert into ASSOCIATIONPOC.RQST_DX (RQST_PID, DX_PID) values (?, ?)
Hibernate: insert into ASSOCIATIONPOC.RQST_DX (RQST_PID, DX_PID) values (?, ?)
org.springframework.dao.InvalidDataAccessResourceUsageException: Could not execute JDBC batch update; nested exception is org.hibernate.exception.SQLGrammarException: Could not execute JDBC batch update
    at org.springframework.orm.hibernate3.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:630)
    at org.springframework.orm.hibernate3.HibernateAccessor.convertHibernateAccessException(HibernateAccessor.java:412)
    at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:424)
    at org.springframework.orm.hibernate3.HibernateTemplate.executeWithNativeSession(HibernateTemplate.java:374)
    at org.springframework.orm.hibernate3.HibernateTemplate.saveOrUpdate(HibernateTemplate.java:748)
    at com.med.repository.AssociationRepository.saveRequestAndDiagnoses(AssociationRepository.java:39)
    at com.med.AssociationApp.createRequestAssociations(AssociationApp.java:85)
    at com.med.AssociationApp.createAndRetrieveRequestTest(AssociationApp.java:222)
    at com.med.AssociationApp.main(AssociationApp.java:197)
Caused by: org.hibernate.exception.SQLGrammarException: Could not execute JDBC batch update
    at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:92)
    at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
    at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:275)
    at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:262)
    at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:182)
    at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:321)
    at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:51)
    at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1206)
    at org.springframework.orm.hibernate3.HibernateAccessor.flushIfNecessary(HibernateAccessor.java:390)
    at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:420)
    ... 6 more
Caused by: java.sql.BatchUpdateException: ORA-00942: table or view does not exist

    at oracle.jdbc.driver.DatabaseError.throwBatchUpdateException(DatabaseError.java:343)
    at oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:10768)
    at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:70)
    at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:268)
    ... 13 more
Executed main().

The XA version seems to have problems accessing a single table, ASSOCIATIONPOC.RQST_DX. Any insights would be appreciated.

A bit more information. The ASSOCIATIONPOC.RQST_DX table that triggers the exception is not mapped as an Hibernate entity. It used to create a many-to-many association between two mapped entity tables.

<hibernate-mapping>
  <class name="com.med.domain.Request" table="ASSOCIATIONPOC.RQST">
    <id name="requestPid" type="long" column="RQST_PID">
      <generator class="native">
        <param name="sequence">ASSOCIATIONPOC.SQ_RQST</param>
      </generator>
    </id>
    <property name="requestId" type="string" column="RQST_ID" />
   
    <set name="diagnoses" table="ASSOCIATIONPOC.RQST_DX" cascade="save-update,persist" lazy="true">
      <key column="RQST_PID" />
      <many-to-many column="DX_PID" class="com.med.domain.Diagnosis" />
    </set>
  </class>
</hibernate-mapping>

How could that make a difference to Atomikos?

The source of my problem was the table needed to be granted permissions:

GRANT SELECT, INSERT, UPDATE, DELETE ON ASSOCIATIONPOC.RQST_DX TO ALINEOAPP;

Somehow the Hibernate transaction manager is able to use the table without having permissions granted, but Atomikos is not.

Jane,

Thanks for sharing!

A bit more thought led me to the correct conclusion. The two tests were configured to connect using different user id's. The Hibernate test's user had system privileges and didn't need to be granted access rights.