All the Perl that's Practical to Extract and Report

Oracle and empty strings

djberg96 on 2004-07-13T19:39:51

Due to some snafu with regards to NULL and Hibernate, the PM asked me to change all instances of NULL for a particular column to ''. Don't look at me, man, I just work here.

So anyway, update some_table set foo = '' where foo is null, right?

BZZT! Little did I know that '' becomes NULL in Oracle. Who knew? Well, I know now. We ended up using ' ' (a single space).

I blame all computer programming languages for confusing me.

NULL is broken

jplindstrom on 2004-07-13T19:50:00

Select may not be broken, but the design of NULL semantics in a lot of databases are FUBAR. It's even worse in Sybase, where NULL, the empty string and ' ' (one space) are all mixed up in weird ways.

Re:NULL is broken

autarch on 2004-07-14T01:37:17

The whole concept of NULL is pretty broken. I think this is covered in Fabian Pascal's Practical Issues in Database Management, and also in Chris Date & Hugh Darwen's Third Manifesto.

Imagine this...

zatoichi on 2004-07-14T00:28:18

We inherited a 3rd party application. ALL tables are NOT NULL. And those tables that are "blank" are padded with SPACES! That is around 1400 tables. I could shoot the original DBA.