Hibernate version: 2.1.6

I have the following hibernate-mapping:

<class name="com.xpn.xwiki.doc.XWikiDocument" table="xwikidoc">
<id name="id" type="long" unsaved-value="any">
<column name="XWD_ID" not-null="true"/>
<generator class="assigned" />
</id>

...
<property name="content" type="string">
<column name="XWD_CONTENT" length="200000" not-null="true" />
</property>

<property name="archive" type="string">
<column name="XWD_ARCHIVE" length="200000" not-null="true"/>
</property>
..

..its not running on oracle because the default mapping in hibernate is varchar2 and that is restricted to 4000 characters.

How can i solve this problem. I would like to avoid to change the sources itself because its not my own code. Its running on mysql but the oracle migration causes problems...

Thanks for any advices!

TNiere

You can customize what column types are used by extending the OracleDialect ... but I don't know, is there an easily accessible column type for larger strings in oracle?

As far as I know, Oracle has only one easy accessible type for long strings->Its LONG. But you can only have one LONG-column for each table. Furthermore it is deprecated.

I would use CLOB but I think hibernate doesn't support these types, does it? If I would like to use it, I would need to change the sources of the application.

>You can extend the OracleDialect.
...Are there any examples? How could that help me?

I think I can't store large strings (>4KB) in Oracle without the datatype CLOB. Therefore I checked how I could get a CLOB mapping done in hibernate.

After searching for some hints in the forum I found the section "Updated Clobs handling for Oracle and Hibernate" in the article http://www.hibernate.org/56.html.

I introduced the two classes (StringClobType and LobCleanUpInterceptor) and changed my mappings to the following:

<property name="content" type="StringClobType">
<column name="XWD_CONTENT" length="200000" not-null="true" />
</property>

<property name="archive" type="StringClobType">
<column name="XWD_ARCHIVE" length="200000" not-null="true" />
</property>

Than I tried to export the schema using the schemaexport-task in ant. Unfortunately it breaks with the following error:

[schemaexport] 11:37:06,347 ERROR main Configuration:add:255 - Could not compile the mapping document
[schemaexport] net.sf.hibernate.MappingException: Could not interpret type: StringClobType
[schemaexport] at net.sf.hibernate.cfg.Binder.getTypeFromXML(Binder.java:934) ...

Can I use UserTypes for schemaexport anyway? If yes, how Can I solve this problem?

Did you probably miss the package in your configuration file?

Yes that was it -> THX
I had to write it full qualified. com.xpn.StringClobType

I can now store data in the clob column. But are there restrictions for HQL statements? e.g. when I search for an entry via an HQL-Select statement "..where clobColumn='normalString';" ->It breaks with this message:

java.sql.SQLException: ORA-00932: inconsistent datatypes: expected - got CLOB

Do you also know what may cause this problem?

THX in advance

In Hibernate 2.1.6, if the property type is set to "text," it maps to a CLOB in Oracle 9 dialect. What's the problem with using that?

using the type "text" is a good hint but batching seem to be a problem with this OracleDialect:

16:08:40,683 WARN http-8080-Processor25 http://127.0.0.1:8080/xwiki/bin/view/Main/WebHome JDBCExceptionReporter:logExceptions:38 - SQL Error: 17090, SQLState: null
16:08:40,714 ERROR http-8080-Processor25 http://127.0.0.1:8080/xwiki/bin/view/Main/WebHome JDBCExceptionReporter:logExceptions:46 - Vorgang nicht zulässig: streams type cannot be used in batching
16:08:40,724 ERROR http-8080-Processor25 http://127.0.0.1:8080/xwiki/bin/view/Main/WebHome JDBCExceptionReporter:<init>:38 - could not insert: [com.xpn.xwiki.doc.XWikiDocument#104408758]
java.sql.SQLException: Vorgang nicht zulässig: streams type cannot be used in batching
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
at oracle.jdbc.driver.OraclePreparedStatement.addBatch(OraclePreparedStatement.java:4073)
at org.apache.commons.dbcp.DelegatingPreparedStatement.addBatch(DelegatingPreparedStatement.java:257)
at org.apache.commons.dbcp.DelegatingPreparedStatement.addBatch(DelegatingPreparedStatement.java:257)
at net.sf.hibernate.impl.BatchingBatcher.addToBatch(BatchingBatcher.java:30)
at net.sf.hibernate.persister.EntityPersister.insert(EntityPersister.java:468)
at net.sf.hibernate.persister.EntityPersister.insert(EntityPersister.java:442)
at net.sf.hibernate.impl.ScheduledInsertion.execute(ScheduledInsertion.java:29)
at net.sf.hibernate.impl.SessionImpl.executeAll(SessionImpl.java:2418)
at net.sf.hibernate.impl.SessionImpl.execute(SessionImpl.java:2371)
at net.sf.hibernate.impl.SessionImpl.flush(SessionImpl.java:2240)
at com.xpn.xwiki.store.XWikiHibernateStore.saveXWikiClassProperty(XWikiHibernateStore.java:1192)

it's not working

That is true. Batching needs to be disabled. It would be nice if Hibernate did this automatically for tables that include CLOBs.

OK, now i disabled batching even if it may has an crucial impact on the performance. But unfortunately that is not enough to make it running:

10:01:59,414 WARN http-8080-Processor25 http://127.0.0.1:8080/xwiki/bin/view/Main/WebHome JDBCExceptionReporter:logExceptions:38 - SQL Error: 17410, SQLState: null
10:01:59,434 ERROR http-8080-Processor25 http://127.0.0.1:8080/xwiki/bin/view/Main/WebHome JDBCExceptionReporter:logExceptions:46 - Keine weiteren Daten aus Socket zu lesen
10:01:59,454 ERROR http-8080-Processor25 http://127.0.0.1:8080/xwiki/bin/view/Main/WebHome JDBCExceptionReporter:<init>:38 - could not insert: [com.xpn.xwiki.doc.XWikiDocument#104408758]
java.sql.SQLException: Keine weiteren Daten aus Socket zu lesen
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
at oracle.jdbc.dbaccess.DBError.check_error(DBError.java:1160)
at oracle.jdbc.ttc7.MAREngine.unmarshalUB1(MAREngine.java:963)
at oracle.jdbc.ttc7.MAREngine.unmarshalSB1(MAREngine.java:893)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:375)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1986)
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:1144)
at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2152)
at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:2035)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2876)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:609)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:233)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:233)
at net.sf.hibernate.impl.NonBatchingBatcher.addToBatch(NonBatchingBatcher.java:22)
at net.sf.hibernate.persister.EntityPersister.insert(EntityPersister.java:468)
at net.sf.hibernate.persister.EntityPersister.insert(EntityPersister.java:442)
at net.sf.hibernate.impl.ScheduledInsertion.execute(ScheduledInsertion.java:29)
at net.sf.hibernate.impl.SessionImpl.executeAll(SessionImpl.java:2418)
at net.sf.hibernate.impl.SessionImpl.execute(SessionImpl.java:2371)
at net.sf.hibernate.impl.SessionImpl.flush(SessionImpl.java:2240)
at com.xpn.xwiki.store.XWikiHibernateStore.saveXWikiClassProperty(XWikiHibernateStore.java:1192)
at com.xpn.xwiki.store.XWikiHibernateStore.saveXWikiClass(XWikiHibernateStore.java:1095)


Somehow, the data doesn't get transferred properly..
If anyone knows any tricks or hints, let me know? THX

Never use the Oracle provided JDBC drivers.

I had problems with CLOBs and the Oracle 9 driver, but the Oracle 10 driver works better. Try upgrading, and use oracle.jdbc.OracleDriver. The oracle.jdbc.driver.OracleDriver class is deprecated. The new driver will work with Oracle 9i databases, too.



What makes you say this? Is there a better alternative?

Is there a maximum length for text properties back-ended by Oracle 9 CLOBs? I'm using Oracle 9i with the odbc14.jar drivers and am not having any problems except when I try to store a string that's too long (still working in determing the magic value, somewhere in the thousands it seems) - I end up with NULL the next time I get the property value from the database.

to rhobot: the magic value is 4000! I am also using the 9i driver...

I had no time to test another jdbc-driver yet! Anyway, it is pretty horible if it just work by turning off batching. Hibernate without batching is really an performance problem.

Thanks, tniere. Have you tried out the Oracle 10 drivers yet? They apparantly work with 9i databases but support updating longer CLOBs (32k?) using prepared statement's setString() method. No, I haven't tried them yet either, just reading over the Oracle site.

I don't have batching disabled in my hibernate.properties file, yet everything is working okay. Is this merely because I'm lucky enough not to have had one of my CLOBs updated in a batch? Or is batching something you have to explicitly enable? Can you do it on a per-session or per-transaction basis?