Wednesday, November 21, 2007

Oracle surprise

I was hunting an issue where inserting an empty string (not null in Java, but with String.lenght()==0) resulted in a constraint violation exception. The column was a varchar2 one and indeed marked as not null.

I asked some people that should know (including a former DBA) and everyone told me that "" != null - always.

Digging around I found the following in an Oracle manual:

Note: Oracle Database currently treats a character value with a length of zero as null. However, this may not continue to be true in future releases, and Oracle recommends that you do not treat empty strings the same as nulls.

So in this case "" == null.

It is ok if one knows it and one can work around, but it came as surprise to me and others.

No comments: