Re: RE: Null data in not null column
- From: Sayan Malakshinov <xt.and.r@xxxxxxxxx>
- To: JBECKSTROM@xxxxxxxxx
- Date: Tue, 15 Apr 2014 23:40:50 +0400
Jeffry,
you're right!
Simple example:
CREATE TABLE ttt(OID NUMBER(15) NOT NULL);
insert into ttt(oid) values(1);
alter table ttt add (ANZEIGE_NAME VARCHAR2(40 BYTE) DEFAULT '' NOT NULL);
select ttt.*,dump(ANZEIGE_NAME,1016) from ttt;
SQL> set null ~null~
SQL> col ANZEIGE_NAME format a20
SQL> col dump format a10
SQL> select ttt.*,dump(ANZEIGE_NAME,1016) dump from ttt;
OID ANZEIGE_NAME DUMP
---------- -------------------- ----------
1 ~null~ NULL
On Tue, Apr 15, 2014 at 11:30 PM, Jeffrey Beckstrom <JBECKSTROM@xxxxxxxxx>wrote:
> In your default clause for the table definition, is there a space between
> the quotes or not. If not, could the default '' be overriding the not null
> check in the table but not for imp/exp?
>
> >>> "Ingrid Voigt" <giantpanda@xxxxxxx> 4/15/14 3:19 PM >>>
>
> Sorry, no fishiness was intended. I ran several versions of the query to
> check if the entries really are nulls,
> made a copy-and-paste-error. Here is a new version:
>
> > set null ~
> > select oid, anzeige_name from usr_user;
> OID ANZEIGE_NAME
> ---------- ----------------------------------------
> 223 ~
> 224 ~
>
>
> But the constraint is said to be enabled and validated:
>
> > select con.constraint_name, con.status, con.validated from
> dba_constraints con, dba_cons_columns col where
> 2 con.owner=col.owner and
> 3 con.constraint_name=col.constraint_name and
> 4 con.table_name = col.table_name and
> 5 col.table_name = 'USR_USER' and
> 6 col.column_name = 'ANZEIGE_NAME';
> CONSTRAINT_NAME STATUS VALIDATED
> ------------------------------ -------- -------------
> SYS_C0013771 ENABLED VALIDATED
>
>
>
> Regards
> Ingrid Voigt
>
>
>
> Gesendet: Dienstag, 15. April 2014 um 20:03 Uhr
> Von: "Mark W. Farnham" <mwf@xxxxxxxx>
> An: oratune@xxxxxxxxx, howard.latham@xxxxxxxxx, giantpanda@xxxxxxx,
> 'ORACLE-L' <oracle-l@xxxxxxxxxxxxx>
> Betreff: RE: Null data in not null column
>
> Likely David is correct.
>
> If in the sqlplus session reporting the data you preface the query with
> set null ~
> then the columns containing actual nulls will be definitively displayed
> visually.
>
> also, your query has a column header of length(ANZEIGE_NAME),
> but that is NOT the query you ran.
>
> Please clear up this fishiness.
>
> mwf
>
>
> From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
> On Behalf Of David Fitzjarrell
> Sent: Tuesday, April 15, 2014 1:28 PM
> To: howard.latham@xxxxxxxxx; giantpanda@xxxxxxx; ORACLE-L
> Subject: Re: Null data in not null column
>
>
> 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[howard.latham@xxxxxxxxx]> wrote:
>
> Set it to ""
>
>
>
>
>
>
> ----- Reply message -----
> From: "Ingrid Voigt" <giantpanda@xxxxxxx[giantpanda@xxxxxxx]>
> To: "oracle-l@xxxxxxxxxxxxx[oracle-l@xxxxxxxxxxxxx]" <
> 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[//www.freelists.org/webpage/oracle-l]
>
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
> .
>
>
--
Best regards,
Sayan Malakshinov
Senior performance tuning engineer
PSBank
http://orasql.org
Other related posts:
- » Re: RE: Null data in not null column - Sayan Malakshinov
