Oracle empty strings

Question

How do you guys treat empty strings with Oracle?

Statement #1: Oracle treats empty string (e.g. '') as NULL in "varchar2" fields.
Statement #2: We have a model that defines abstract 'table structure', where for we have fields, that can't be NULL, but can be "empty". This model works with various DBMS; almost everywhere, all is just fine, but not with Oracle. You just can't insert empty string into a "not null" field.
Statement #3: non-empty default value is not allowed in our case.

So, would someone be so kind to tell me - how can we resolve it?

Shon

Answer 1

This is why I've never understood why Oracle is so popular. They don't actually follow the SQL standard, based on a silly decision they made many years ago.

The Oracle 9i SQL Reference states (this has been there for at least three major versions):

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

But they don't say what you should do. The only ways I've ever found to get around this problem are either:

• have a sentinel value that cannot occur in your real data to represent NULL (e.g, "deoxyribonucleic" for a surname field and hope that the movie stars don't start giving their kids weird surnames as well as weird first names :-).
• have a separate field to indicate whether the first field is valid or not, basically what a real database does with NULLs.

Triggerfish

"based on a silly decision they made many years ago" Yes, because backwards compatibility is never an issue. Oracle can safely change the behavior of empty strings without breaking boatloads of code that rely on this behavior -.- – Antique

@Null, there are any number of ways you can fix this without breaking compatibility. One jumped into my head within a second of reading your comment, that of allowing a flag to be set for specific code that would run it in "conforming" mode. That way, if you don't explicitly set the flag for all of your code, you have perfect compatibility. If you want specific code to treat NULLs correctly, set the flag for it. Once you're convinced you have no rubbish code any more, set the flag globally. These "kludge" bits have been around at least since the days of Data General :-) – Triggerfish

That would be similar to SQL Server's ANSI NULLS (eg: SET ANSI NULLS ON), but having different environment behaving differently can be a source of great confusion. – Antique

Answer 2

Are we allowed to say "Don't support Oracle until it supports the standard SQL behaviour"? It seems the least pain-laden way in many respects.

If you can't force (use) a single blank, or maybe a Unicode Zero Width Non-Break Space (U+FEFF), then you probably have to go the whole hog and use something implausible such as 32 Z's to indicate that the data should be blank but isn't because the DBMS in use is Orrible.

Subatomic

All hail to the Zero-Width Non-Break Space, hero and our rescue, keeper of the Insanity, bane of all Oracle mantainers! – Moffat

Answer 3

Empty string and NULL in Oracle are the same thing. You want to allow empty strings but disallow NULLs.

You have put a NOT NULL constraint on your table, which is the same as a not-an-empty-string constraint. If you remove that constraint, what are you losing?

Briar

Sharp model definition, nothing else. – Shon

Answer 4

I found this "issue" created 15 years ago, has been viewed 4k times, and no one ever offers a "solution".

Since Oracle is not changing this (or at least we don't know if they will...) here's what I did to resolve this.

CASE WHEN RTRIM(columnname) IS NULL THEN ' ' ELSE RTRIM(columnname) END

Or use TRIM or both LTRIM and RTRIM (depending on the Oracle version):

CASE WHEN TRIM(columnname) IS NULL THEN ' ' ELSE TRIM(columnname) END
CASE WHEN LTRIM(RTRIM(columnname)) IS NULL THEN ' ' ELSE LTRIM(RTRIM(columnname)) END

I tested all of these in Oracle 19c.

I believe forums are better used to provide solutions rather than complain about the issue... I hope this helps someone.

Untold