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: [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).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Mercadante, Thomas F
> 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).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:[EMAIL PROTECTED]
title:DB Services Lead
x-mozilla-cpt:;-4832
fn:Daniel Fink
end:vcard
