5 Replies Latest reply on Apr 4, 2007 11:45 AM by freerider

    Errors with global transactions on oracle

    freerider

      Hi,
      I'm trying to move an application, which uses jBPM, from Postgres to Oracle 8.1.7.

      My setup is:
      JBoss 4.0.3SP1
      jBPM 3.1 which uses Hibernate 3.1.2 which is configured by Spring 1.2.7.

      My problem is, that oracle doens't allow the following SQL in a global transaction:
      analyze table MOSSWORKFLOW_TEST1.JBPM_ACTION estimate statistics
      Error encountered: ORA-02089

      Here is my xa-datasource configuration:
      <xa-datasource>
      <jndi-name>MossJbpmXADS</jndi-name>
      <track-connection-by-tx/>
      <isSameRM-override-value>false</isSameRM-override-value>
      <xa-datasource-class>oracle.jdbc.xa.client.OracleXADataSource</xa-datasource-class>
      <xa-datasource-property name="URL">jdbc:oracle:thin:@IP:1521:dbName</xa-datasource-property>
      <xa-datasource-property name="User">user</xa-datasource-property>
      <xa-datasource-property name="Password">pwd</xa-datasource-property> <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name>
      <no-tx-separate-pools/>
      <type-mapping>Oracle8i</type-mapping>
      </xa-datasource>


      I haven't found any entry concerning this problem in the forum.
      Thanks for helping me out.

        • 1. Re: Errors with global transactions on oracle
          mputz

          Hi,

          have a look at this discussion in the Hibernate forum:
          http://forum.hibernate.org/viewtopic.php?t=961680

          which reveals its the getIndexInfo() call that causes the ANALYZE TABLE call, as discussed in this Oracle forum entry:

          http://forums.oracle.com/forums/thread.jspa?threadID=210782

          Unfortunately this doesn't reveal which Oracle driver has a fix for this, but I suggest you try the latest driver - why Oracle8 btw?

          Regards,
          Martin

          • 2. Re: Errors with global transactions on oracle
            kukeltje

            Martin,

            He probably works for an insurance company..... they still use COBOL-61 as well ;-)

            Ronald (Just kidding)

            • 3. Re: Errors with global transactions on oracle
              freerider

              Hi Martin,
              thanks for your tips. I tried a few drivers 10.2.0.3, 9.2.0.8 and 8.1.7.1. But now it seems that the problem moved a little bit.

              org.springframework.jdbc.BadSqlGrammarException: Hibernate operation: could not get table metadata: JBPM_ACTION; bad SQL grammar [???]; nested exception is java.sql.SQLException: ORA-02089: COMMIT is not allowed in a subordinate session
              java.sql.SQLException: ORA-02089: COMMIT is not allowed in a subordinate session

              I tried to set the property hibernatetool.metadatadialect=org.hibernate.cfg.reveng.dialect.OracleMetaDataDialect but after regenerating the hbm files I still get this error.
              Do you have any complete example for an XA datasource with correct hibernate.properties?

              One solution in a forum was to change to a local datasource which is a bad idea when you are in a global transaction.

              Why we are using Oracle8i? Hmm... I'm not the DBA and I'm not working for an insurance company. But I like the remark.

              • 4. Re: Errors with global transactions on oracle
                kukeltje

                I hope the DBA does not make the decisions:

                Product Obsolescence / Desupport Information:
                Oracle Corporation announces the end of Error Correction Support for Oracle Database version(s) 8.1.7 (8i) & 8.1.7.x (8i) on the following platform(s): Microsoft Windows XP, Microsoft Windows 2000, Linux x86, HP-UX PA-RISC, IBM OS/390 (z/OS) version(s) ALL - OSDI, excluding MPM, Microsoft Windows 98, HP Tru64 UNIX, HP Alpha OpenVMS, Solaris Operating System (x86), IBM AIX-Based Systems version(s) 5L, Fujitsu-Siemens BS2000/OSD, Solaris Operating System (SPARC), Microsoft Windows NT for Intel, effective 31-DEC-2004.

                • 5. Re: Errors with global transactions on oracle
                  freerider

                  We just tried it with Oracle 10.2.0.1 and we still get the same error (The jdbc driver has version 10.2.0.3).

                  ORA-02089: COMMIT is not allowed in a subordinate session
                  java.sql.SQLException: ORA-02089: COMMIT is not allowed in a subordinate session

                  Is this still a jdbc driver problem or is there a way to disable the analyze table query on the db from hibernate?

                  Thanks for any help.