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.
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.
Oracle stores data (obtained from the documentation):
No datatype is saved inside the data, just the data length and also the data itself.
NULL happens between two posts with values, it's saved like a single byte meaning column has length
NULLs aren't saved whatsoever.
To keep value
Oracle must store 5 bytes:
04 74 65 73 74.
However, to keep both a clear string along with a
Oracle just must set data length to
Very wise in case your data should be saved on
20 Mb hard disk drives that cost
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
'' being exactly the same factor.
VARCHAR to complete this type of distinction will break tons of code.
To repair it, they re-named
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
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