Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version: 3.05

Name and version of the database you are using: Oracle 8

I have a problem with OracleDialect - I picked OracleDialect and not Oracle9Dialect as it's for "any version". I assume this means 8, since Oracle9Dialect is for 9 and 10.

I have a base class with three union-subclass entries. If I do a select for the base class (which checks subclasses too) it generates an SQL union. To do this, it has to fill in the missing columns for the smaller sized tables. It does this by adding a bunch of nullif(0,0) or nullif('x','x') entries into the select for those tables.

This is coming from

public String getSelectClauseNullString(int sqlType) {
String literal;
switch(sqlType) {
case Types.VARCHAR:
literal = "'x'";
break;
case Types.CHAR:
literal = "'x'";
break;
case Types.DATE:
literal = "'2000-1-1'";
break;
case Types.TIMESTAMP:
literal = "'2000-1-1 00:00:00'";
case Types.TIME:
literal = "'00:00:00'";
default:
literal = "0";
}
return "nullif(" + literal + ',' + literal + ')';
}

but nullif doesn't work, Oracle 8 just bombs out as it's an "invalid column name". In particular, in a SQL session:

select id from table -- works
select id, nullif(0,0) from table -- errors out

Is there any easy way for me to get rid of the nullifs? is there an alternate OracleDialect perhaps? or do I have to make a custom dialect based on it. Creative suggestions welcome, I am only looking for a pragmatic solution. Upgrading Oracle is not a viable one for me, at least not just to work around this hibernate quirk.

Thanks in advance for all advice,
Oliver

Well, you'll have to figure out how to get a "typed" null in oracle8. I don't know how myself. Then customizing the dialect will be easy.

You mean the following?


Maybe, you'll also need these ones to get a type-safe null value.

Nope, it can't use a column reference in it.

How about you just create and test a patch for the Dialect.

NULLIF(..) is definitely not available prior to Oracle 9i.

Sorry, I don't have an Oracle database here to test, but what about the following:



This will return type-safe null values as it does with NULLIF(..).

Sorry, I missed some breaks using the current method.



By the way, aren't there also missing these breaks in the current method?

Thanks, committed. Apparently that code was never tested on ora8 :-\

wow, thanks guys :)

I'm going ahead and using the TO_X(NULL) in a custom dialect for now.

-Oly