One of the things that seems to puzzle many Oracle newbies who have experience of other programming languages is that Oracle treats a zero length string as NULL. Over the Christmas period the OTN PL/SQL forum featured an entertaining but ultimately futile discussion on the philosophical ramifications of this behaviour. Personally I cannot see why there should be any more meaning in an empty string than in a NULL but many people think otherwise. And if you deeply believe that there is a meaningful distinction between an empty string and NULL then Oracle's indifference must be galling, if not heretical.
Interestingly, some of the people in this camp might be Oracle engineers. As Laurent Schneider has recently pointed out, Oracle have implemented different behaviour for LOBs. A zero length CLOB is not NULL. We could infer from this that, given the opportunity, Oracle might choose to treat empty VARCHAR2 variables this way too. Then again, it may just be an artefact of the LOB implementation. Either way. the upshot is that Oracle is unlikely to change the existing behaviour simply because such a change would probably break too many existing applications.
Of course, every languages have their quirks, especially with something as slippery as NULL. And developers in other programming languages do funny things with NULL, as this posting from the Daily WTF demonstrates.
3 comments:
I enjoyed Padders' suggestion, "If only someone could, say, knock up a wooden model of NULL in their bedroom or something I'm sure it would explain everything." By this point the discussion had become pretty surreal with Albert making one outrageous claim after another and replying to criticisms with rambling non-sequiturs, much like our friend George in the Great Identifier Class RDBMS Debate last summer.
I saw at one point in Oracle's docs that they were going to change that behavior and make empty strings not be the same as nulls as some indeterminate point in the future. (I think it's an ANSI compatibility issue.) I don't expect that it'll happen anytime too soon...
Doesn't it actually say: "Do not use the VARCHAR datatype. Use the VARCHAR2 datatype instead. Although the VARCHAR datatype is currently synonymous with VARCHAR2, the VARCHAR datatype is scheduled to be redefined as a separate datatype used for variable-length character strings compared with different comparison semantics." There is no mention of nulls and there will not be any global change to "strings". I've also yet to be convinced that this is even covered by ANSI.
Post a Comment