15

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

Community
  • 1
  • 1
Steve B.
  • 55,454
  • 12
  • 93
  • 132
  • This question has been posted as related, though it doesn't address the "why": http://stackoverflow.com/questions/1171196/difference-between-varchar-and-varchar2 – Steve B. Aug 12 '09 at 19:23

5 Answers5

33

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.

Community
  • 1
  • 1
Quassnoi
  • 413,100
  • 91
  • 616
  • 614
  • The only reason is historical? And not fixed for 25 years? How could that be, given that it costs so much? ;) – Steve B. Aug 12 '09 at 19:30
  • 2
    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
  • 3
    @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 06:02
  • Their current documentation states "Oracle Database 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." It has said this for about 15 years though..... – Gary Myers Sep 09 '11 at 10:23
  • This recommendation by Oracle always struck me as somewhat ridiculous. How on earth are you supposed to not treat empty strings the same as nulls if the database forces you to do it this way? – Frank Schmitt Jun 11 '13 at 06:51
  • To be fair, after 20 years working with Oracle I am now driven crazy by having to treat empty strings and nulls differently in PostgreSQL -- it's very much a matter of perspective. – David Aldridge Jun 11 '13 at 08:25
  • 1
    @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
  • @Quassnoi That's exactly my point. How are you supposed to filter out empty strings? You cannot compare with '' - you *have* to check for IS NULL (unless you consider ugly hacks like nvl(my_column, 'value_that_hopefully_never_exists') <> 'value_that_hopefully_never_exists'. – Frank Schmitt Jun 11 '13 at 14:06
  • 2
    @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
  • @Quassnoi That's a sensible suggestion - I'll try to stick to that in the future. – Frank Schmitt Jun 11 '13 at 14:14
3

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.

Rob van Wijk
  • 17,555
  • 5
  • 39
  • 55
1

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

0

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

Ian Carpenter
  • 8,346
  • 6
  • 50
  • 82
0

@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
tuinstoel
  • 7,248
  • 27
  • 27
  • @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