My Oracle Support Banner

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

Last updated on AUGUST 04, 2018

Applies to:

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


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)


To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.