We have an application that, for legacy reasons, has most of the string columns in the database defined to use the sql type char[n], not varchar.

It has been working okay using another database dialect; i.e. DB2. However, when we tried running it on an Oracle 10g instance, we're getting empty results sets on our queries.

The team working on the Oracle trial has traced the issue to the query parameter binding. The parms are being bound as VARCHAR, but the column data is char[n]. Apparently, in this test with Oracle, no matching records are found. To verify this was the cause, they changed the column datatypes from char[n] to varchar2(n) and all the queries behave as under DB2.

However, the project is not prepared to migrate our columns to varchar because apparently there is / was a big performance hit in DB2 when using varchar.

So for now, I'm looking for a workaround that could allow us to support an Oracle instance without rejiggering all of the columns to varchars. I thought maybe a specialized Oracle10gDialect could address the issue, but in reading the Dialect JavaDocs, I'm just not sure that's the right answer. I have to believe this has been encountered before, so am hopeful there's a "standard" way to handle it.

I would welcome any advice on how we can work past this issue. (Without changing our column types to varchar!)

FWIW: Hibernate 3.6.5, dialect: Oracle10gDialect

Thank you.

--
jack

For lurkers...

I've found the varchar parameters used in a PreparedStatement when querying against char[n] columns is a known issue with an Oracle database. This StackOverflow discussion illustrates the issue:

http://stackoverflow.com/questions/3451269/parameterized-oracle-sql-query-in-java

The solutions posed there were to:

1) Change the database columns to varchar2 columns
2) Pad the query parameter to exactly match the column size and use an Oracle-specific PreparedStatement method setCHAR(CHAR)
3) Use the Oracle-specific PreparedStatement method setFixedCHAR(CHAR) to automatically pad the CHAR value

The first is not an option for the project at this point. The second and third choices may be doable, but makes the code a bit ridiculous when supporting multiple database dialects.

I'm still hoping the Hibernate community can offer a Hibernate-way out of this vendor-specific mess.

--
jack