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?

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.

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.

Answer 3

What is the reason behind you statement, "non-empty default value is not allowed in our case"

Answer 4

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?