RE: Empty String is interpreted as NULL

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:techlist_at_voyager.phys.utk.edu]
> 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. Received on Fri Jul 18 2003 - 14:36:26 CDT