Oracle is "nearly" SQL-92 compliant. Oracle does in fact think that empty strings are equivalent to NULL. What's worse is that some developers depend on this behavior in their code. I thought I'd seen warnings about the possibility this may change in the future in the Oracle docs but I couldn't find anything after a quick search (the '' == NULL is documented).
I don't think there's any workaround. Oracle simply does not allow empty VARCHAR2 strings (well, it does, but it calls them NULL). And what's even worse than that is that, depending on the interface you use, if you try to use a string made up entirely of spaces Oracle will "helpfully" strip trailing spaces, you'll end up with an empty string, and you'll get the same error again. There are workarounds for this, it depends on the interface you're using. > -----Original Message----- > From: Reuben D. Budiardja [mailto:[EMAIL PROTECTED] > Sent: Friday, July 18, 2003 4:10 PM > To: Multiple recipients of list ORACLE-L > Subject: Empty String is interpreted as NULL > > > Hello all, > Suppose I have this table > > SQL> DESC FRUIT > Name Null? Type > ----------------------------------------- -------- > ORANGE NOT NULL VARCHAR2(10) > APPLE NOT NULL VARCHAR2(10) > > If I do this insert: > > SQL> / > INSERT INTO FRUIT VALUES ('hello', '') > * > ERROR at line 1: > ORA-01400: cannot insert NULL into ("LIGHTCONE"."FRUIT"."APPLE") > > I got an error cannot insert NULL. But, what if I meant is to > insert empty > string '' ? Certainly empty string is NOT equal to NULL values. > > So how do I get around this? > > Thanks in advance for any help. > LEGAL NOTICE: Unless expressly stated otherwise, this message is confidential and may be privileged. It is intended for the addressee(s) only. Access to this e-mail by anyone else is unauthorized. If you are not an addressee, any disclosure or copying of the contents or any action taken (or not taken) in reliance on it is unauthorized and may be unlawful. If you are not an addressee, please inform the sender immediately. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Sarnowski, Chris INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
