Oracle 中的empty string和NULL

How to Insert Empty String Into Oracle Not Null Column to Avoid ORA-01400 Using Trigger (文档 ID 1224216.1)

APPLIES TO:

Oracle Server - Enterprise Edition - Version: 9.2.0.8 and later   [Release: 9.2 and later ]
Information in this document applies to any platform.

GOAL

Given a table where a character column is defined as not null:
 

create table tester (id number not null, dat varchar2(30) default ' ' not null);

Why does the default value not get used in the following statements?  
 

insert into tester values (1, '');
insert into tester values (1, null);

ERROR at line 1:
ORA-01400: cannot insert NULL into ("REFRESH"."TESTER"."DAT")

How might you still insert a record in these circumstances?
 

Note that the string value '' (no space) is seen by Oracle as a null per documentation:
Oracle� Database SQL Language Reference
11g Release 2 (11.2)
Part Number E17118-03
E021-02, CHARACTER VARYING data type (Oracle does not distinguish a zero-length VARCHAR string from NULL)

 

SOLUTION

The "DEFAULT" column option does not work in this scenario because a value, albeit null, was provided in the insert for the dat column.  The default value is only used if the column is omitted from the insert statement.

To utilize the default value defined at the column-level, the insert would need to be written as either:
 

insert into tester (id)  values (1);
insert into tester values (1, default);

In the case where a value is supplied but seen as null by Oracle, then use a trigger to provide a valid value:
 

create or replace trigger tester_null_ck_trig
before insert or update of dat on tester
for each row
declare
begin
if :new.dat is null then
:new.dat := ' ';
end if;
end;
/

insert into tester values (1, '');
insert into tester values (1, null);

 

REFERENCES

http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/ap_standard_sql003.htm#g14847

转帖者注：

如下是Oracle 10g文档中对于此点的描述：

Oracle对核心SQL:2003的遵从性

https://docs.oracle.com/cd/B19306_01/server.102/b14200/ap_standard_sql003.htm