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).
