CODEWITHSUNDEEP
databaseoracle-database
Steve B.
Steve B.

Reputation: 57333

Oracle not distinguishing between nulls and empty strings?

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

Upvotes: 15

Views: 5419

Answers (5)

Quassnoi
Quassnoi

Reputation: 425863

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):

alt text

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

Upvotes: 33

Erwin Smout
Erwin Smout

Reputation:

Which is why smart people like Date say that you should NEVER use nulls.

(No, I have to be precise. It's in fact only just a single one of the almost hundreds of reasons he has mentioned over this past few decades to support that claim.)

EDIT

I actually also wanted to respond to this :

"Making VARCHAR to do such a distinction will break tons of code."

Yeah, and surely, breaking at least the spirit of the standard by replacing the "empty string" by null on every update is a lesser evil ?

(Note : null is not equal to anything, not even itself, so after assigning the empty string to a column, oracle will give you a value in that column that is NOT the same as the value that you said you wanted to appear there. Wow.)

Upvotes: 1

tuinstoel
tuinstoel

Reputation: 7316

@Ian, a reply to you.

Oracle triggers can reference the table they are created on:

create table t (id number(10) );

create or replace trigger  t_bir before insert  on  t for each row
declare
  l_id t.id%type;
begin
  select id
  into   l_id
  from   t
  where  id = :new.id;
exception
  when no_data_found then 
    null;
end;
/


SQL> insert into t values (20);

1 row is created.


SQL> select * from t;

        ID
----------
        20

Upvotes: 0

Rob van Wijk
Rob van Wijk

Reputation: 17705

You might want to read this lenghty and at times funny discussion about this exact subject more than two years ago on OTN: http://forums.oracle.com/forums/thread.jspa?threadID=456874&start=0&tstart=0

Regards, Rob.

Upvotes: 3

Ian Carpenter
Ian Carpenter

Reputation: 8626

Looks like Oracle have said that this behaviour may be change in a future release. When and which release it will be is not mentioned.

If you have access to metalink look at note: 1011340.6 (unfortunately because of restrictions I am unable to copy the contents of the note here)

If you don't have access to metalink then look look at the following from the 10g release 2 documentation here

Upvotes: 0

Related Questions