Take the 2-minute tour ×

Apparently oracle doesn't seem to distinguish between empty strings and nulls. E.g.

Select name from TABLE_A where id=100;
  100  null

Update TABLE_A set NAME='' where id=100;
  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.)

share|improve this question
This question has been posted as related, though it doesn't address the "why": stackoverflow.com/questions/1171196/… –  Steve B. Aug 12 '09 at 19:23
add comment

5 Answers

up vote 29 down vote accepted

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.

share|improve this answer
Cost != quality. I have never used Oracle, but check out Lotus Notes. That software also costs a lot of $$$, but few people would say that it is a high quality application. –  Ed S. Aug 12 '09 at 19:33
+1 "waiting for the last person to die"... I can just picture it... –  Steve B. Aug 12 '09 at 21:42
@Steve It's an issue of backward compatibility. This behaviour brings some problems for new development, but changing it to null != empty string would bring thousands times more problems with code suddenly not functioning properly after upgrade to a new Oracle version. –  Michal Pravda Aug 13 '09 at 6:02
@FrankSchmitt: I think they mean something "don't rely on str IS NULL to filter out empty strings" or similar, as empty strings may stop satisfying this condition. –  Quassnoi Jun 11 '13 at 13:26
@FrankSchmitt: in Oracle, you don't have to but you should to. A mere str IS NULL is just fine in Oracle (as of now) but in other engines you should add str IS NULL OR str = ''. Their point is that you stick to the second one in Oracle as well. –  Quassnoi Jun 11 '13 at 14:09
show 7 more comments

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.

share|improve this answer
Nice discussion! –  Quassnoi Aug 12 '09 at 20:38
add comment

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


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

share|improve this answer
add comment

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

share|improve this answer
add comment

@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
  l_id t.id%type;
  select id
  into   l_id
  from   t
  where  id = :new.id;
  when no_data_found then 

SQL> insert into t values (20);

1 row is created.

SQL> select * from t;

share|improve this answer
@tuinstoel: only because you are using the VALUES clause. If you switch to the INSERT SELECT: SQL> insert into t select 20 from dual; insert into t select 20 from dual * ERROR at line 1: ORA-04091: table RWIJK.T is mutating, trigger/function may not see it ORA-06512: at "RWIJK.T_BIR", line 4 ORA-04088: error during execution of trigger 'RWIJK.T_BIR' –  Rob van Wijk Aug 12 '09 at 21:10
-------- I know. –  tuinstoel Aug 12 '09 at 21:12
add comment

Your Answer


