Menu
According to official 11g docs
Oracle Database currently treats a character value with a length of zero as null. However, this may not continue to be true in future releases, and Oracle recommends that you do not treat empty strings the same as nulls.
Consider a function getVersion that returns a varchar2 that might possibly be '':
l_version := x.getVersion;
if l_version is null then
return 'V.1.0';
end if;
This will work correctly on current Oracle 11g, but might break as soon as future Oracle versions treat '' differently than null.
The only way I see to do the above future proof is:
if l_version is null or l_version = '' then
Is there a less cumbersome way?
943
According to the ANSI SQL 1992 Transitional standard, a zero-length or empty string is not the same as NULL. Ensure that applications do not treat empty strings and NULL values equivalently."
A VARCHAR2 column can store a value that ranges from 1 to 4000 bytes. It means that for a single-byte character set, you can store up to 4000 characters in a VARCHAR2 column. By default, Oracle uses BYTE if you don't explicitly specify BYTE or CHAR after the max_size .
The VARCHAR2 data type specifies a variable-length character string in the database character set. You specify the database character set when you create your database. When you create a table with a VARCHAR2 column, you must specify the column length as size optionally followed by a length qualifier.
Use the IS [NOT] EMPTY conditions to test whether a specified nested table is empty, regardless whether any elements of the collection are NULL . The condition returns a boolean value: TRUE for an IS EMPTY condition if the collection is empty, and TRUE for an IS NOT EMPTY condition if the collection is not empty.
Assuming that you are using varchar2 throughout your code, l_version is null will be future proof.
Oracle created the varchar2 data type when the ANSI standards declared that varchar should treat NULL and the empty string as separate entities. The intention was that the behavior of the varchar2 data type would remain consistent going forward while varchar in the future could use the new standard NULL comparison semantics. Of course, today varchar and varchar2 are synonyms for each other and they have been for at least a couple of decades so the odds that Oracle actually changes the behavior of the varchar data type in the future is pretty low.
When you look at the documentation for the VARCHAR2 and VARCHAR data types, it talks about the comparison semantics for VARCHAR potentially changing in the future. Unfortunately, it's not explicit that the comparison semantics they're talking about are the equivalence (or lack thereof) between NULL and the empty string. But since VARCHAR is an ANSI standard data type and the only difference in VARCHAR comparison semantics between Oracle and the ANSI standard is whether the empty string is NULL, that's the generally accepted interpretation.
Do not use the VARCHAR datatype. Use the VARCHAR2 datatype instead. Although the VARCHAR datatype is currently synonymous with VARCHAR2, the VARCHAR datatype is scheduled to be redefined as a separate datatype used for variable-length character strings compared with different comparison semantics.
106
If the behaviour does change then are you not going to want to be able to distinguish between empty string and null when such a thing is possible? In other words, future proof code that treats empty strings and nulls as the same will probably need revisiting in the future anyway.
Also, there are currently something like 100 billion trillion null strings stored in Oracle databases worldwide that will continue to be null.
So my advice is to forget about it, and just use IS NULL.
6
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
