| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Empty String is interpreted as NULL
This is a multi-part message in MIME format.
--------------380082512FB8359D19AA507C Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit
How about inserting 'NULL'? No, I am not kidding... The following is taken from an actual system...
columnA has a NOT NULL constraint
columnA has a CHECK constraint with a list of acceptable values, one of them is 'NULL'
BTW, I don't recommend either approach. NULL has a special meaning to Oracle (3-valued logic) and you should pay close attention to how you use it.
"Mercadante, Thomas F" wrote:
>
> Reuben,
>
> While I agree that an empty string is not logically equal to a null, Oracle
> interprets an empty string in INSERT and UPDATE statements as a NULL. So
> you really do not have a choice here. If you have the need to insert an
> empty string into a column, you have two choices:
>
> - Define a character to represent an empty string and insert that character
> (pretty dumb suggestion)
>
> - Change the table to allow null values in that column, and perform the
> INSERT as your example showed.
>
> Good Luck and hope these helped.
>
> Tom Mercadante
> Oracle Certified Professional
>
> -----Original Message-----
> Sent: Friday, July 18, 2003 4:10 PM
> To: Multiple recipients of list ORACLE-L
>
> 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.
>
> Reuben D. Budiardja
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Reuben D. Budiardja
> INET: techlist_at_voyager.phys.utk.edu
>
> 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: ListGuru_at_fatcity.com (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).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Mercadante, Thomas F
> INET: NDATFM_at_labor.state.ny.us
>
> 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: ListGuru_at_fatcity.com (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).
--------------380082512FB8359D19AA507C
Content-Type: text/x-vcard; charset=us-ascii;
name="daniel.fink.vcf"
Content-Transfer-Encoding: 7bit Content-Description: Card for Daniel Fink Content-Disposition: attachment;
begin:vcard
n:Fink;Daniel
tel;cell:303.808.3282
tel;work:303.272.3225
x-mozilla-html:TRUE
adr:;;;;;;
version:2.1
email;internet:daniel.fink_at_sun.com
title:DB Services Lead
Received on Fri Jul 18 2003 - 14:46:14 CDT
 |
 |