Message-Id: <25956.338517@fatcity.com> From: Daniel Fink Date: Fri, 18 Jul 2003 13:46:14 -0600 Subject: 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@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@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@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@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; filename="daniel.fink.vcf" 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@sun.com title:DB Services Lead