Hi, I am unable to create the required temporary tables HT_ on an oracle database. I am using the ejb3 entity manager and annotations plus the core. in my persistence.xml I have set up 1 XA datasource, and 1 non XA datasource.

It is failing in AbstractStatementExecutor.createTemporaryTableIfNecessary(final Queryable persister, final SessionImplementor session).

If I create all the temporary tables prior to my tests by taking the scripts from PersistentClass.temporaryIdTableDDL, then I am OK (the creation will fail with a log.debug trace, but the error will be ignored and the tables will obviously be there when needed).

debug variables show:
connection=JTAConnection_weblogic_jdbc_wrapper_XAConnection_oracle_jdbc_driver_OracleConnection (id=9824)
stmnt=Statement_oracle_jdbc_driver_OracleStatement (id=9825)


has anybody gotten any luck with temp tables/ejb3/hibernate/oracle?
thanks,
v.

Hibernate version: core 3.2.0cr2, hibernate-entitymanager-3.2.0.CR1, hibernate-annotations-3.2.0.CR1.jar

Code between sessionFactory.openSession() and session.close():
em.createQuery("delete from Manager").executeUpdate();

Full stack trace of any exception that occurs:

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

at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.oci8.OCIDBAccess.check_error(OCIDBAccess.java:2355)
at oracle.jdbc.oci8.OCIDBAccess.executeFetch(OCIDBAccess.java:1760)
at oracle.jdbc.oci8.OCIDBAccess.parseExecuteFetch(OCIDBAccess.java:1921)
at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2191)
at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:2064)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2989)
at oracle.jdbc.driver.OracleStatement.executeUpdate(OracleStatement.java:891)
at weblogic.jdbc.wrapper.Statement.executeUpdate(Statement.java:433)
at org.hibernate.hql.ast.exec.AbstractStatementExecutor$1.doWork(AbstractStatementExecutor.java:116)
at org.hibernate.hql.ast.exec.AbstractStatementExecutor.createTemporaryTableIfNecessary(AbstractStatementExecutor.java:137)
at org.hibernate.hql.ast.exec.MultiTableDeleteExecutor.execute(MultiTableDeleteExecutor.java:78)
at org.hibernate.hql.ast.QueryTranslatorImpl.executeUpdate(QueryTranslatorImpl.java:391)
at org.hibernate.engine.query.HQLQueryPlan.performExecuteUpdate(HQLQueryPlan.java:259)
at org.hibernate.impl.SessionImpl.executeUpdate(SessionImpl.java:1134)
at org.hibernate.impl.QueryImpl.executeUpdate(QueryImpl.java:94)
at org.hibernate.ejb.QueryImpl.executeUpdate(QueryImpl.java:43)
at com.lodh.dummy.ejb.session.DummyServiceMgr.emptyTable(DummyServiceMgr.java:45)

Name and version of the database you are using:
Oracle 9.2.0 with OCI driver ojdbc14.jar

The generated SQL (show_sql=true):
create global temporary table HT_Manager (id number(10,0) not null) on commit delete rows

Debug level Hibernate log excerpt:

Yes sure, I test quite frequently on Oracle. In fact most of that functionality was developed against an Oracle 10g database.

It was not, however tested through XA connections as I do not have access to XA stuff in our testsuite. But I am confused by the error message you get back from Oracle. Is this somehow a nested transaction? Oracle has very stransge rules about when DDL statement can and cannot be performed.

I was assuming that you would not use the XA datasource for that kind of work, but the non-jta-datasource. I believe what happens is that the DDL causes an implicit commit, which is not allowed on the xa connection. here is an example of suspending the current transaction to execute safely the ddl:

http://beehive.apache.org/docs/1.0m1/sy ... Guide.html

cheers,
v.

Hey,

I am facing the same problem you encountered with XADatasources and global temp tables.
I just run it with the latest hibernate 3.2.4sp1, with no luck. It has not been fixed yet.

vsevel, It sounds like you solved the problem by suspending the transaction, running that DDL create, and then resuming the transaction again...Did that work for you?
Any plan for the hibernate guys to include that in the build?

I went to that url you posted, but it is invalid.Thnkx

Hello,

Is there a solution to this problem? I am facing the same problem with Hibernate 3.2.5.GA and XADataSources.

I looked in the code for running the DDL: (org.hibernate.engine.transaction.Isolater)

- XA transaction is suspended
- a new XA transaction is created for the DDL
- the DDL is executed (create global temporary table HT_jed49 (IDTPRO number(10,0) not null) on commit delete rows), which causes an implicit autocommit and the failure at the execution of the DDL as the commit is not allowed inside the XA transaction

It seems to me like a bug in Hibernate.

hello Jeremie,
did you consider trying an updated version? 3.2.5 is quite old and hardly supported by volunteers - do your really need to keep that version?

Hello,

Thanks for the reply.
Problem does not occur anymore with version 3.6.0. We will try to upgrade our application with this version.

_______ _ _
Jérémie

Hello,

In fact, the problem is still present in the version 3.6.0 and XA datasources, the same mechanism as in the previous versions:

- XA transaction is suspended
- a new XA transaction is created for the DDL
- the DDL is executed (create global temporary table HT_jed49 (IDTPRO number(10,0) not null) on commit delete rows), which causes an implicit commit and the failure at the execution of the DDL as the commit is not allowed inside the XA transaction

The logs:

2010-11-12 17:50:11,111 DEBUG [org.hibernate.engine.transaction.Isolater] surrounding JTA transaction suspended [TransactionImple < ac, BasicAction: -53edfbbe:bb2:4cdd6a4a:101 status: ActionStatus.RUNNING >]
2010-11-12 17:50:11,533 DEBUG [org.hibernate.hql.ast.exec.MultiTableUpdateExecutor] unable to create temporary id table [ORA-02089: COMMIT is not allowed in a subordinate session
]2010-11-12 17:50:11,549 DEBUG [org.hibernate.engine.transaction.Isolater] surrounding JTA transaction resumed [TransactionImple < ac, BasicAction: -53edfbbe:bb2:4cdd6a4a:101 status: ActionStatus.RUNNING >]
2010-11-12 17:50:11,549 DEBUG [org.hibernate.jdbc.AbstractBatcher] about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
2010-11-12 17:50:11,549 DEBUG [org.hibernate.jdbc.ConnectionManager] opening JDBC connection
2010-11-12 17:50:11,549 DEBUG [org.hibernate.SQL] insert into HT_jed53 select securityb2x0_.IDTPCP as IDTPCP from jed53 securityb2x0_ inner join jed30 securityb2x0_1_ on securityb2x0_.IDTPCP=securityb2x0_1_.IDTPCP where IDTPCP=?
2010-11-12 17:50:11,564 DEBUG [org.hibernate.jdbc.AbstractBatcher] about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
java.sql.SQLException: ORA-00918: column ambiguously defined

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)