Apparently oracle does not appear to differentiate 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 not think about worthwhile reason Oracle could be created to behave by doing this (will it do that in sqlplus too?-I am being able to access via a java interface, the content recommended used a php client).

Wouldn't you a minimum of wish to distinguish length from undefined length? Is a recognized problem? Intentional behavior for many specific purpose? A lengthy-running dispute in database theory? What gives?

(It was motivated by Matt Solnit's response to this question.)

Oracle is extremely super old.

In 80's if this was created (and before there have been any standards) they thought is was advisable, and given then way Oracle stores its values, it truly was.

Here's how Oracle stores data (obtained from the documentation):

alt text

No datatype is saved inside the data, just the data length and also the data itself.

When the NULL happens between two posts with values, it's saved like a single byte meaning column has length 0 (really, 0xFF). Trailing NULLs aren't saved whatsoever.

To keep value 'test', Oracle must store 5 bytes: 04 74 65 73 74.

However, to keep both a clear string along with a NULL, Oracle just must set data length to 0.

Very wise in case your data should be saved on 20 Mb hard disk drives that cost 5,000$ each.

Later, once the standards made an appearance, it had not been such advisable any longer, but with that time there already were numerous of code depending on NULL and '' being exactly the same factor.

Making VARCHAR to complete this type of distinction will break tons of code.

To repair it, they re-named VARCHAR to VARCHAR2 (which isn't part of any standard), mentioned that VARCHAR2 will never separate a NULL as well as an empty string and advised everyone to make use of this datatype rather.

Description of how the are most likely waiting during the last individual who used a VARCHAR in Oracle database to die.

You might like to look at this lenghty and sometimes funny discussion relating to this exact subject a lot more than 2 yrs ago on OTN: http://forums.oracle.com/forums/thread.jspa?threadID=456874&start=0&tstart=0

Regards, Take advantage of.

Appears like Oracle have stated this behavior might be alternation in the next release. When and which release it will likely be isn't pointed out.

If you can get metalink take a look at note: 1011340.6 (regrettably due to limitations I'm not able copying the items in the note here)

Without having use of metalink take a look consider the following in the 10g release 2 documentation here

@Ian, an answer for you.

Oracle triggers can reference the table they're produced 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