Oracle not distinguishing between nulls and empty strings?
database, oracle
Solution
`Oracle` is very very very old.
Back in `80's` when it was developed (and before there were any standards) they thought is was a good idea, and given then way `Oracle` stores its values, it really was.
Here's how `Oracle` stores data (taken from the documentation):
No datatype is stored within the data, only the data length and the data itself.
If the `NULL` occurs between two columns with values, it's stored as a single byte meaning column has length `0` (actually, `0xFF`). Trailing `NULL`s are not stored at all.
So to store the value `'test'`, `Oracle` needs to store 5 bytes: `04 74 65 73 74`.
However, to store both an empty string and a `NULL`, `Oracle` just needs to set data length to `0`.
Very smart if your data are to be stored on `20 Mb` hard drives that cost `5,000$` each.
Later, when the standards appeared, it wasn't such a good idea anymore, but by that time there already were lots and lots of code relying on `NULL` and `''` being the same thing.
Making `VARCHAR` to do such a distinction will break tons of code.
To fix it, they renamed `VARCHAR` to `VARCHAR2` (which is not a part of any standard), stated that `VARCHAR2` will never distinguish between a `NULL` and an empty string and urged everybody to use this datatype instead.
Now they are probably waiting for the last person who used a `VARCHAR` in `Oracle` database to die.
Problem
Apparently oracle doesn't seem to distinguish between empty strings and nulls. E.g. ``` Select name from TABLE_A where id=100; ID NAME 100 null Update TABLE_A set NAME='' where id=100; SELECT --> ID NAME 100 null SELECT length(NAME) FROM TABLE_A WHERE id=100; null ``` I can't think of any good reason why Oracle would be built to behave this way (does it do this in sqlplus as well?-I'm accessing through a java interface, the article referenced used a php client). Wouldn't you at least want to distinguish 0 length from undefined length? Is this a known issue? Intentional behavior for some specific purpose? A long-running dispute in database theory? What gives? (This was prompted by Matt Solnit's answer to this question.)