Re: Null data in not null column

The default indeed does not have any spaces, so this looks like the solution.

Very interesting... so the workaround for the errors will probably be

• export normally
• create table with null
• import with ignore=y
• save the data of the column anzeige_name
• drop and re-add column

Or maybe just enable novalidate...

In any case, I'd much prefer not to change anything in the source database. (and not replace the values by spaces). There are other strange things in there, and I have no influence on the application.

Thanks everybody again.

Regards
Ingrid Voigt

On 15.04.2014 21:40, Sayan Malakshinov wrote:
> 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_at_gcrta.org <mailto:JBECKSTROM_at_gcrta.org>> 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_at_gmx.net <mailto:giantpanda_at_gmx.net>>
> 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_at_rsiz.com <mailto:mwf_at_rsiz.com>>
> An: oratune_at_yahoo.com <mailto:oratune_at_yahoo.com>,
> howard.latham_at_gmail.com <mailto:howard.latham_at_gmail.com>,
> giantpanda_at_gmx.net <mailto:giantpanda_at_gmx.net>, 'ORACLE-L'
> <oracle-l_at_freelists.org <mailto:oracle-l_at_freelists.org>>
> 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_at_freelists.org
> <mailto:oracle-l-bounce_at_freelists.org>
> [mailto:oracle-l-bounce_at_freelists.org
> <mailto:oracle-l-bounce_at_freelists.org>] On Behalf Of David Fitzjarrell
> Sent: Tuesday, April 15, 2014 1:28 PM
> To: howard.latham_at_gmail.com <mailto:howard.latham_at_gmail.com>;
> giantpanda_at_gmx.net <mailto:giantpanda_at_gmx.net>; 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_at_gmail.com
> <mailto:howard.latham_at_gmail.com>" <howard.latham_at_gmail.com
> <mailto:howard.latham_at_gmail.com>[howard.latham_at_gmail.com
> <mailto:howard.latham_at_gmail.com>]> wrote:
>
> Set it to ""
>
>
>
>
>
>
> ----- Reply message -----
> From: "Ingrid Voigt" <giantpanda_at_gmx.net
> <mailto:giantpanda_at_gmx.net>[giantpanda_at_gmx.net
> <mailto:giantpanda_at_gmx.net>]>
> To: "oracle-l_at_freelists.org
> <mailto:oracle-l_at_freelists.org>[oracle-l_at_freelists.org
> <mailto:oracle-l_at_freelists.org>]" <oracle-l_at_freelists.org
> <mailto:oracle-l_at_freelists.org>[oracle-l_at_freelists.org
> <mailto:oracle-l_at_freelists.org>]>
> 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
> --
> http://www.freelists.org/webpage/oracle-l[http://www.freelists.org/webpage/oracle-l]
> <http://www.freelists.org/webpage/oracle-l%5Bhttp://www.freelists.org/webpage/oracle-l%5D>
>
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
> .
>
>
>
>
> --
> Best regards,
> Sayan Malakshinov
> Senior performance tuning engineer
> PSBank
> http://orasql.org

--
http://www.freelists.org/webpage/oracle-l