Oracle considers the empty string '' to be the same as null. This differs from what most SQL dialect do and it prevents compliance with ANSI SQL.

Proposed Idea

Have a database configuration that would make oracle compliant by:

• Considering null/empty string as different
  • in comparison
  • while stored (inserting then selecting an empty string should return an empty string)
• Modify concatenation operator behave as specified in the spec ('a' || null = null, and 'a' || '' = 'a')
• Modify functions having a string as input to comply with the spec. For instance LENGTH(null) should yield null but LENGTH('') should yield 0.

This configuration would need to be opt-in to avoid breaking changes for existing oracle application.

Note:

It seems that VARCHAR2 was created to keep VARCHAR  datatype available to support the standard behaviour in the future.

However I a database level configuration would be needed to configure how empty string literals should be considered.

Motivation

The goal of such idea is to be able to make SQL vendor independant application easier to write.

Some application developped with standard SQL in mind and actually making a difference between '' and null  have a hard time being made compatible with Oracle.

ANSI SQL: empty character string vs null behaviour

Even though we can find lot of places on the web people saying that in standard SQL empty character string should not be considered null I couldn't find in the SQL specification a place that explicitely states that.

However what the spec says the following

Concatenating character string with null should yield null

From ISO/IEC 9075-2:2003 (E) 6.28 <string value expression>:

    <concatenation> ::= <character value expression> <concatenation operator> <character factor>
    ...
    b) Let S1 and S2 be the result of the <character value expression> and <character factor>, respectively. Case:
        i) If either S1 or S2 is the null value, then the result of the <concatenation> is the null value.

So the spec says 'foo' || null = null but in oracle 'foo' || null = 'foo'.

Even if not explicited in the spec 'foo' || '' should yield 'foo'. (I can't think of an argument to have anything else).

Hence one cannot comply with the spec if considering '' and null because it requires need different results for input '' and null.

Length(null) should yield null value

From ISO/IEC 9075-2:2003 (E) 6.27 <numeric value function>

<length expression> ::=
<char length expression>
| <octet length expression>
<char length expression> ::=
{ CHAR_LENGTH | CHARACTER_LENGTH } <left paren> <string value expression>
[ USING <char length units> ] <right paren>
...
General Rules
1) If the value of one or more <string value expression>s, <datetime value expression>s, <interval value
expression>s, and <collection value expression>s that are simply contained in a <numeric value function>
is the null value, then the result of the <numeric value function> is the null value.

So LENGTH(null) should yield null but LENGTH('') should yield 0.

Hence one cannot comply with the spec if considering '' and null because it requires need different results for input '' and null.

<literal> are used to specify non-null values

From ISO/IEC 9075-2:2003 (E) 5.3 <literal>.

Function: Specify a non-null value.

This implies that '' should not be null or not be a valid string literal.

But in oracle '' is considered to be null.

Update:

Rephrasing to:

* clarify what the proposed idea.

* summarize evidences from SQL specification.