There are 2 approaches to resolve this problem
1 modify hbm(or schema) accept null, remove not-null constraints.
2 hack Hibernate, use listeners, such as PreInsertEventListener, PreUpdateEventListener, replace empty String with some special character, for example '\r'. and then use some Post- listener restore empty string if needed. But I known little about Hibernate event model.

I tried the 2nd on xwiki 1.0B5, work with Oracle 10G XE, seemed most functions is ok, except some static HQL, for example, find Orphaned Pages script

#set ($query = "where doc.parent is null or doc.parent='' order by doc.name asc")
should replaced as:
#set ($query = "where doc.parent is null or doc.parent=chr(13) order by doc.name asc")

Attachments is my test config and sample code.

I have zero experience on hibernate, and does not test the approach carefully.

1. Oracle take empty strings as NULL.
This is standard practice in commercial databases and this said in theory (ex. Kodd). Empty means NULL. It is a bad idea to put some chars like '\r13' instead of NULL. Only one way exists - change schema to adequate definition the information model.
I examine data in tables (ex. xwikidoc). There are a lot of documents without parent. Parent is absent. Parent is nothing. Parent is NULL !. But in schema definition parent is not null ! This is a logical bag in the schema.

2. Column types
Oracle has few data types for strings up to 4000 bytes. Most useful - varchar2. For very long text - better type is clob (up to (4G*database block size). In most cases 16-32G.
For binary data - blob. Also huge.

3. Hibernate.
There are few issues. First - you can specify sql-type="...lob". Good thing, but needs to provide different configuration files for different databases. Most interest issue is change column type in schema definition file from "string" to "text". Hibernate will automatically use right column types.
Problem exists with Hibernate column type "long" (integer long). It conflicts with Oracle type "Long" (long text up to 2G). Here you can specify sql-type="number" or change column type to int or double. I prefer single Hibernate configuration for different databases.

4. Clob and BLOB processing.
Oracle deals with LOBs as with streams. For setting up Hibernate you needs to specify Java system property
hibernate.cglib.use_reflection_optimizer=false
hibernate.jdbc.use_streams_for_binary=true

5. There are few points in code with NullPointerExeption because parent is null. I found few holes, but it is a much more.

6. ID's and sequences.
Oracle has perfect implementation of sequences - autogeneration of unique ID. It is very useful for primary key. But XWiki make ID's using hash algorithm. It is not a good idea, but works with open source DB. Hibernate has parameters for generate unique ID and works with different DB includes mySql. It is a good idea for using hash for fast search, but bad idea to use it for primary key. I think next version should use sequence feature.

I think, I can prepare complete solution and patch next week.

You seem to be well versed into all this so that's really cool. What you say makes sense to me.

Joachim, WDYT?

Awaiting your patch

Feel free to discuss it on the mailing list if you want.

Thanks
-Vincent

I haven't estimate the change of schema definition side effect, so I choice a dirty but quick approach I think.
Modify property type from 'string' to 'text', can work well with oracle(sql type as clob).

Thanks
Joachim

For [4. Clob and BLOB processing.
Oracle deals with LOBs as with streams. For setting up Hibernate you needs to specify Java system property
hibernate.cglib.use_reflection_optimizer=false
hibernate.jdbc.use_streams_for_binary=true ] Do we need to change any Java source code for XWiki?

I am looking forward to a universal hibernate.cfg.xml which works well with both Oracle and MySQL

This model and Hibernate fix is really going to pave the road to let XWiki enter the corporate deployment.

Way to go!

Let me know if I can be of help on the clob bit.
Niels

2. Use mapping file with oracle-specific features with xwiki.oracle.hbm.xml

Difference is in large binary objects processing. In first case, Hibernate will create columns type "long row" for binary data. In second case, Hibernate will create columns type "blob". Only two fields exists now with type "binary".

The "long raw" datatype has limit with 2G, bad manageability and problems with data processing. Also exists limit with one column per table.
The BLOB - up to (4G*database block size). In most cases 16-32G.
BLOB is better but needs to support two map files.

------
How to install XWiki with Oracle Database.

Enviroment
1.Oracle Database 10g 10.2.0 XE, PE, SE or EE ... Any

2. Create user xwiki in database.

3. Oracle JDBC drivers from http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/htdocs/jdbc_10201.html
10.2.0.3 version

4. Copy ojdbc14.jar, orai18n.jar into lib directory.

5. In file WEB-INF/hibernate.cfg.xml uncomment reference to oracle mapping file and comment/delete reference to xwiki.hbm.xml
<mapping resource="xwiki.oracle.hbm.xml"/>

6. In file WEB-INF/hibernate.cfg.xml uncomment section for Oracle database. Specify correct connection URL.

7. Start xwiki. Run Java with system properties
-Dhibernate.jdbc.use_streams_for_binary=true
-Dhibernate.cglib.use_reflection_optimizer=true
Other way - use file hibernate.properties. Put it somewhere in classpath. Ex. WEB-INF/classes

I test XWiki on Oracle Application Server (Standalone OC4J). And I use container-managed connections with pooling. Works well.
There are one more incantations - additional vendor-specific deployment profile for OC4J.

Some intallation notes are now needed to be written to configure XWiki for Oracle