I have been working on getting all database structure changes complete, I seemed to be having an issue with the way our oracle datasource is configured (see below). Does anyone know how to configure the oracle datasource to fix this?
When using a non-transaction-enabled DataSource, a "java.sql.SQLException: ORA-01002" error will occur when creating a CMP entity bean which has a field mapped to Oracle CLOB/BLOB.
The EJB container inserts a row which has CLOB/BLOB columns as follows:
insert into the table initializing the LOB using EMPTY_BLOB() or EMPTY_CLOB().
retrieve the LOB locator using select for update statement.
modify the LOB.
Although the connection from a non-transaction-enabled DataSource is in auto-commit mode, Oracle LOB locators that are used to write cannot span the transactions. So the transaction is closed at step 1 and then ORA-1002 occurs at step 2.
use the Oracle 10g jdbc driver instead of 9i and it works ha ha ha