Geek Answers Handbook

Oracle blank lines

How do you guys handle blank lines with Oracle?

Statement # 1 : Oracle treats an empty string (for example, '') as NULL in the "varchar2" fields.
Statement # 2 : We have a model that defines an abstract "table structure", where for fields there may not be NULL, but may be "empty". This model works with various DBMS; almost everywhere, everything is just fine, but not with Oracle. You simply cannot insert an empty string into the non-null field.
Statement # 3 : a non-empty default value in our case is not valid.

So, will anyone be so kind as to tell me - how can we solve it?

+7
oracle orm varchar2
source share
3 answers

That's why I never understood why Oracle is so popular. They actually do not follow the SQL standard based on the stupid decision they made many years ago.

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

Oracle currently treats a character value with a length of zero as null. However, this cannot continue in future releases, and Oracle recommends that you do not treat blank lines as well as zeros.

But they do not say what you should do. The only ways I have ever come across to solve this problem are:

  • have a sentinel value that cannot be present in your real data to represent NULL (for example, "deoxyribonucleic" for the surname field and hope that movie stars do not start giving their children strange names as well as strange names :).
  • has a separate field indicating whether the first field is valid or not, basically what the real database does with NULL.
+6
source share

Can I say "Do not support Oracle until it supports standard SQL behavior"? This is perhaps the least painful path in many ways.

If you cannot force (use) one space or perhaps Unicode Zero Width Non-Break Space (U + FEFF), then you will probably have to go through the whole pig and use something implausible, for example 32 Z indicate that the data must be empty, but not because the DBMS used is forced.

+2
source share

The empty string and NULL in Oracle are one and the same. You want to allow blank lines, but disable NULL.

You have a NOT NULL constraint in your table, which matches a non-empty row constraint. If you remove this restriction, what do you lose?

+1
source share

More articles:


All Articles