Hi I have some problem while saving XML string to the database XMLTYPE. Hibernate is throwing this exception only if the xml file is more than 20kb approx. for smaller sized xmls its working fine.

i am providing the exception stack trace here. please help me in this.


Hibernate version: 3

Mapping documents:
<hibernate-mapping>
<class name="foo.bar.Test" table="TEST" schema="MY_SCHEMA">
<id name="SeqId" type="java.lang.Long">
<column name="SEQ_ID" precision="22" scale="0" />
<generator class="sequence">
<param name="sequence">TEST_ID_SEQ</param>
</generator>
</id>
<many-to-one name="TestStaging" class="foo.bar.TestStaging" fetch="select">
<column name="STATUS_ID" precision="22" scale="0" not-null="true" />
</many-to-one>
<property name="title" type="java.lang.String">
<column name="TITLE" length="100" not-null="true" />
</property>
<property name="testId" type="java.lang.String">
<column name="TEST_ID" length="20" />
</property>
<property name="versnNbr" type="java.lang.String">
<column name="VERSN_NBR" length="10" />
</property>
<property name="publshdUsrId" type="java.lang.Long">
<column name="PUBLSHD_USR_ID" precision="22" scale="0" not-null="true" />
</property>
<property name="usrType" type="java.lang.String">
<column name="USR_TYPE" length="20" not-null="true" />
</property>
<property name="dttime" type="java.util.Date">
<column name="DTTIME" length="7" not-null="true" />
</property>
<property name="divsnName" type="java.lang.String">
<column name="DIVSN_NAME" length="20" not-null="true" />
</property>
<property name="gradeLvlNbr" type="java.lang.String">
<column name="GRADE_LVL_NBR" length="5" />
</property>
<property name="prgrmName" type="java.lang.String">
<column name="PRGRM_NAME" length="20" />
</property>
<property name="pkgType" type="java.lang.String">
<column name="PKG_TYPE" length="15" not-null="true" />
</property>
<property name="xmlData" type="java.lang.String">
<column name="XML_DATA" />
</property>

<sql-insert>insert into MY_SCHEMA.TEST (STATUS_ID, TITLE, TEST_ID, VERSN_NBR, PUBLSHD_USR_ID, USR_TYPE, DTTIME, DIVSN_NAME, GRADE_LVL_NBR, PRGRM_NAME, PKG_TYPE, XML_DATA)
values(?,?,?,?,?,?,?,?,?,?,?,XMLType(?))

</sql-insert>
</class>
</hibernate-mapping>

Code between sessionFactory.openSession() and session.close():
Long generatedId = (Long)session.save(test);

Full stack trace of any exception that occurs:

2006-03-18 18:49:14,723 DEBUG [org.hibernate.util.JDBCExceptionReporter] - Could not execute JDBC batch update [insert into MY_SCHEMA.TEST (STATUS_ID, TITLE, TEST_ID, VERSN_NBR, PUBLSHD_USR_ID, USR_TYPE, DTTIME, DIVSN_NAME, GRADE_LVL_NBR, PRGRM_NAME, PKG_TYPE, XML_DATA)
values(?,?,?,?,?,?,?,?,?,?,?,XMLType(?))

]
java.sql.BatchUpdateException: ORA-01461: can bind a LONG value only for insert into a LONG column

at oracle.jdbc.driver.DatabaseError.throwBatchUpdateException(DatabaseError.java:367)
at oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:8726)
at weblogic.jdbc.wrapper.PreparedStatement.executeBatch(PreparedStatement.java:169)
at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:57)
at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:174)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:226)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:136)
at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:274)
at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:730)
at foo.bar.ManagerDAOImpl.saveTest(ManagerDAOImpl.java:402)
at foo.bar.delegator.StorageDelegator.importTest(StorageDelegator.java:372)
at foo.bar.ejb.StorageManagerEJB.importContent(StorageManagerEJB.java:291)
at foo.bar.ejb.tStorageManager_ag84gz_EOImpl.importContent(StorageManager_ag84gz_EOImpl.java:253)
at foo.bar.ejb.IngestionManagerEJB.starImportFromQueue(IngestionManagerEJB.java:204)
at foo.bar.ejb.IngestionManager_sbisub_EOImpl.starImportFromQueue(IngestionManager_sbisub_EOImpl.java:46)
at foo.bar.ejb.StorageListnerMDB.onMessage(StorageListnerMDB.java:114)
at weblogic.ejb20.internal.MDListener.execute(MDListener.java:370)
at weblogic.kernel.ExecuteThread.execute(ExecuteThread.java:219)
at weblogic.kernel.ExecuteThread.run(ExecuteThread.java:178)
2006-03-18 18:49:14,723 WARN [org.hibernate.util.JDBCExceptionReporter] - SQL Error: 1461, SQLState: 72000
2006-03-18 18:49:14,723 ERROR [org.hibernate.util.JDBCExceptionReporter] - ORA-01461: can bind a LONG value only for insert into a LONG column

Name and version of the database you are using:

Oracle 10g

The generated SQL (show_sql=true):
Could not execute JDBC batch update [insert into MY_SCHEMA.TEST (STATUS_ID, TITLE, TEST_ID, VERSN_NBR, PUBLSHD_USR_ID, USR_TYPE, DTTIME, DIVSN_NAME, GRADE_LVL_NBR, PRGRM_NAME, PKG_TYPE, XML_DATA)
values(?,?,?,?,?,?,?,?,?,?,?,XMLType(?))

Debug level Hibernate log excerpt:

When I used Oracle thin driver, I got the same error message while trying to insert large XML file into the database. However, the error went away after I changed the driver to BEA WebLogic 9.2 Type 4 JDBC Oracle driver (Non-XA).

Finally, after four months, I am very glad to know that this forum is live.

Any way thank you very much Mr. Thuang
Even I was able to solve this problem by moving to weblogic driver from Oracle driver.
And I am really feeling very bad for failing to update that in the forum.