Oracle not distinguishing between nulls and empty strings?

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

– Steve B. Aug 12 '09 at 19:30 1 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 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 6:02 excellent explanation. – Mark Harrison Aug 13 '09 at 7:52.

You might want to read this lenghty and at times funny discussion about this exact subject more than two years ago on OTN: forums.oracle.com/forums/thread.jspa?thr... Regards, Rob.

Nice discussion! – Quassnoi Aug 12 '09 at 20:38.

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, 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: 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.

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

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.