Re: Null data in not null column
- From: David Fitzjarrell <oratune@xxxxxxxxx>
- To: "howard.latham@xxxxxxxxx" <howard.latham@xxxxxxxxx>, "giantpanda@xxxxxxx" <giantpanda@xxxxxxx>, ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 15 Apr 2014 10:28:22 -0700 (PDT)
SQL> insert into emp(empno) values ("");
ERROR:
ORA-01741: illegal zero-length identifier
SQL>
SQL>
SQL>
SQL>
SQL> insert into emp(empno) values ('');
insert into emp(empno) values ('')
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("GRIBNAUT"."EMP"."EMPNO")
SQL>
TJ is correct, the NOT NULL constraint was added after the NULL values were
entered; it was created NOVALIDATE so it wouldn't fail on creation due to the
existing NULL values. It's possible to disable the constraint on the source
table before the export then enable it novalidate in the destination database
after the table has been imported. Of course you would also need to enable the
constraint novalidate in the source database after the export has completed.
David Fitzjarrell
Primary author, "Oracle Exadata Survival Guide"
On Tuesday, April 15, 2014 11:13 AM, "howard.latham@xxxxxxxxx"
<howard.latham@xxxxxxxxx> wrote:
Set it to ""
----- Reply message -----
From: "Ingrid Voigt" <giantpanda@xxxxxxx>
To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
Subject: Null data in not null column
Date: Tue, Apr 15, 2014 4:53 PM
Hi,
one of our customers' databases has a table with a not null column containing
empty data.
Do you know how this can be set up? I need to copy the table (via exp/imp) and
hit either ORA-01400 (when defining not null
before insert) or ORA-02296 (when I insert first and later modify the column).
More details: Windows 2008 R2
Oracle 11.2.0.3 + Patch 27 (January 2014) Table definition (more columns left
out): CREATE TABLE USR_USER
( OID NUMBER(15) NOT NULL, ANZEIGE_NAME
VARCHAR2(40 BYTE) DEFAULT '' NOT NULL,
)
TABLESPACE USERS; And the data: > select oid, anzeige_name,
'.'||anzeige_name||'.' from usr_user; OID ANZEIGE_NAME
LENGTH(ANZEIGE_NAME)
---------- ---------------------------------------- -------------------- 223 224
(more rows left out) These are not strings consisting of spaces, but really
empty ones, i.e. nulls. Insert into the table without specifying anzeige_name
also hits ORA-01400, the condition
is active. The corresponding check constraint is enabled and validated. I'd
appreciate help in reproducing this. Thank you.
Ingrid Voigt
-- //www.freelists.org/webpage/oracle-l
Other related posts:
