Oracle and Bedework

This is really more like Oracle and Hibernate.

There have been a number of people trying this out and helping to determine a solution...

  • D. AlexandEr? LSE
  • Julian Ball Queens
  • Chris Mann Maryland
  • Skip Warnick Maryland

I believe the essence of the problem is that Oracle in the past did not support long strings in the same was as other data types. There have been various workrounds. Following are notes which attempt to arrive at some resolution.

Schema changes and progress

Following some success with hand-editing the generated schema the following changes have been made.

Remove most text types

Type text results in a clob. These can be handled by the Oracle 10g jdbc driver as long as they are under 32k. After that, they can be handled by setting the SetBigStringTryClob property. So far, most text types have been changed to a length limited string type. Some may be too short.

Split internationalized Strings into 2 tables

Some of the strings take part in where clauses, for example we check for the uniqueness of a location name. This doesn't work (at least without some changes) if the string is a clob.

There are now two string tables, one for shorter string which allow searching and one for clobs which does not allow searching. At the moment only the event description uses the long string table. Other properties may be moved there later.

Dialect

The notes below suggest that the OracleDialect should be used, not the Oracle9Dialect. One problem seems to be that the OracleDialect maps timestamps on to date whereas we would rather have timestamp.

Trying Oracle9Dialect

Lengths

I spent some time trying to figure out how to get the length specifier looking like "char(1 char)" or "varchar2(4000 char)".

However, the Oracle data types guide at "http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14220/datatype.htm#i3253" has this to say:

The parameter NLS_LENGTH_SEMANTICS decides whether a new column of character datatype uses byte or character semantics. The default length semantic is byte. If all character datatype columns in a database use byte semantics (or all use character semantics) then users do not have to worry about which columns use which semantics. The BYTE and CHAR qualifiers shown earlier should be avoided when possible, because they lead to mixed-semantics databases. Instead, the NLS_LENGTH_SEMANTICS initialization parameter should be set appropriately in the server parameter file (SPFILE) or initialization parameter file, and columns should use the default semantics.

So I just generate the default - whatever that may be.

And having said all that, it turns out that Oracle9Dialect generates e.g. 255 char.

Don't use long strings

Not a great solution. The limit is 4k which is inadequate in general. It might just have worked for public events, it's generally not a great idea to pack the calendar with giant strings. However, we have a problem with timezone definitions which now exceed the 4k limit (5.4k is the max) and other long strings will probably follow.

Some information and attempts at solutions to this problem follow

Oracle page on use of clobs

http://www.oracle.com/technology/sample_code/tech/java/codesnippet/jdbc/clob10g/handlingclobsinoraclejdbc10g.html

From the hibernate forum

In this forum http://forum.hibernate.org/viewtopic.php?t=935274&highlight=multiple we have the following:

FWIW, we are using CLOBs successfully with the Oracle 10 driver supplied by Oracle. It works fine on Oracle 9i databases, too. No special UserType? required. We just set the property types to "text". Although we do have to disable batching.

A followup message which claims some problems has the config: 

<property name="hibernate.jdbc.batch_size">0</property>
<property name="hibernate.jdbc.use_streams_for_binary">   true</property>
<property name="SetBigStringTryClob">true</property>

xwiki solution

xwiki posted this possible (edited) solution which may or may not be applicable

Oracle support

An Oracle database can be used with XWiki but requires extra setup work to complete. The following changes are required:

  • in hibernate.cfg.xml:
    • Change the hibernate dialect to "OracleDialect" .... DO NOT USE Oracle9Dialect as this reduces the ability to correctly identify mappings using clob/blob elements.
    • Change the connection.drivers_class to "oracle.jdbc.driver.OracleDriver"
  • Add the ojdbc14.jar to the lib directory.

Oracle 10g

  1. Create the database and user.
  2. Import the schema-
  3. Configure the database properties as described above. Out of the box, you will probably experience problems with foreign key relations or deadlocks (from the OSCaching) resulting from database errors.
  4. Add the hibernate user type class from http://www.hibernate.org/56.html and then change the type on any large objects (more than 4K) to the ClobType? above.

Oracle 9i

Oracle 9i is not easily supported by Xwiki. There are two problems here and solution discussions below.

  • Oracle and Hibernate do not handle the CLOB / BLOB support required to activate xwiki correctly.
  • Oracle does not distinguish between the empty string "" and the null value for varchar2 fields. (This is supposed to be fixed with 10g)

xwiki Solutions:

  • Clob handling: xwiki has a "JdbcOraWrapper" wrapping driver to solve Clob writing with a String (This only fixes inserts as Updates seem to work appropriately)
  • String handling: (To be fully addressed)
  • Update the "JdbcOraWrapper" classes to recognize " " as "" and convert on all getString(...), setString(...), and updateString(...) methods.
  • Fix the code?