Hibernate, easing the port to Oracle 10g from Sqlserver

There is a fair bit of doco around for migrating to Oracle for Sqlserver (see http://otn.oracle.com), but here are some issues I had this week.

Case Insensitivity, Incompatibilities.

10g has a couple of settings to allow '=' '>' and '<' operators case insensitive, set database parameters, nls_comp=ansi and nls_sort=binary_ci.

Don't forget function based indexes need to be created with an nls_sort=binary_ci option.

NOTE: that this doesn't work for the 'like' condition, you will need to either use regexp_like fucntion, or fall back to the old method of using an uppercased function based index on the column(s) in question.

Good discussion of case sensitivity issues can be found at asktom

NULL Ordering differences between Sqlserver and Oracle.

Oracle uses ansi standard ordering, nulls appear at the end of a list, sqlserver is opposite. Also sqlserver appears to do an implicit order by identitycol, which oracle definitely doesn't do.

Hibernate primary key generator

How cool is hibernate, we were already using generator-class="native" so it chose between identity or sequence, just needed to add the @hibernate.generator-param name="sequence" value="<tablename_seq\>"

Lobs, JDBC Drivers and Database Issues.

lob support, 10g has really improved in terms of lob support, clob's don't need anything special setup as long as they are <32K, std getString can be used for access. For >32K add a connection property called "SetBigStringTryClob". see handlingclobsinoraclejdbc10g

Blob support in the 10g jdbc thin driver has really improved, but setBlob isn't really supported, it takes an java.sql.Blob but downcasts to oracle.sql.BLOB.

Orion added to the issue since it's ejb and cmt default data-source wrap the underlying DataSource and only give out java.sql.Connection's. Standard configuration is to set ejb-location element which adds the OrionCMTDataSource wrapper. I think the doco could be improved in this area of the dtd configuration, it is mentioned in a one liner in the lengthly discussion on datasources.

I assumed removing this setting and allowing the pooled OracleDataSource to handle CMT should work, but this had a very bad transactional side affect. We had one method running under required transaction, using a local interface to another method that has requires a new transaction.

But by not using ejb-location, it actually didn't create a new transaction.

Maybe I missed something in the configuration but failing this we have to have another pooled datasource that can be lookedup and create specific connections that can be handed off to the Hibernate Session.

Smaller DataType Issues

smalldatetime, (why would you even bother to use this, it is restricted to 6th june 2079, do you really need to save those extra bytes?) We have several existing sql statements that are hacked due to performance, indexes weren't being used if we binded Date objects for conditions on those columns, so instead they are currently just hardcoded literally. Even jdbc escaped date format didn't have the desired effect of working with the indexes. Don't know if this in an issue with the JSQLConnect driver?

money datatype, Oracle doesn't have one, so instead we added length="2" to our hibernatedoclet property declarations, which results in desired precision to a number(19,2).

Hibernate Formula's

We had some properties based on formula's that used sqlserver specific date functions. We could actually have used the SQL Extension functions that allow for {fn TIMESTAMPDIFF(SQL_TSI_DAY, update_timestamp, :start_date) }

I started looking into this, Hibernate dialect needs to be modified so it recognizes SQL_TSI_DAY, as a no arg function, hibernate will assume it is an object property.

Unfortunately Oracle doesn't support the SQL Extension functions so you can't keep the formula's database agnostic.

Oracle Flashback

10g has a new Flashback feature, which while sound's great, is a pain in the butt in the dev environment. Since drop table now keeps a copy of the table and must be purged.

We currently use hloader for loading data into our tables for any of the databases supported by our app and we also use it to drop all tables in our schema or database instead of standard hibernate schema tasks, so it had to be enhanced to allow extra options for drop commands.

Tweet This Post