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

Last updated on JULY 17, 2017

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

Sign In with your My Oracle Support account

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

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms