7

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?

Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
Vugluskr
  • 1,426
  • 1
  • 12
  • 12

3 Answers3

7

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.
paxdiablo
  • 854,327
  • 234
  • 1,573
  • 1,953
  • 1
    "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 -.- – NullUserException Aug 27 '11 at 15:30
  • @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 :-) – paxdiablo Aug 27 '11 at 22:34
  • 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. – NullUserException Aug 27 '11 at 22:50
3

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.

Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
  • All hail to the Zero-Width Non-Break Space, hero and our rescue, keeper of the Insanity, bane of all Oracle mantainers! – quetzalcoatl Mar 29 '18 at 00:50
1

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?

WW.
  • 23,793
  • 13
  • 94
  • 121