Re: Empty String is interpreted as NULL
Thanks for all replies. Right now I think I can afford just make the column NULL, so that's what I'll do. But still, something doesn't feel quite right with empty string == NULL :). RDB On Friday 18 July 2003 03:22 pm, 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- > 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 > - > ORANGENOT 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 -- Reuben D. Budiardja Department of Physics and Astronomy The University of Tennessee, Knoxville, TN - /"\ ASCII Ribbon Campaign against HTML \ / email and proprietary format X attachments. / \ - Have you been used by Microsoft today? Choose your life. Choose freedom. Choose LINUX. - -- 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).
Re: Empty String is interpreted as NULL
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 > - > ORANGENOT 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
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:[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 > - > ORANGENOT 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).
RE: Empty String is interpreted as NULL
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 - ORANGENOT 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).