Re: Null data in not null column
- From: Mohamed Houri <mohamed.houri@xxxxxxxxx>
- To: "Mark W. Farnham" <mwf@xxxxxxxx>
- Date: Tue, 15 Apr 2014 21:54:07 +0200
I am sorry I didn't went through all the answers. But if you are
complaigning about an export/import of a table which has a not null column
and which results into a null value when imported in the other table then
it might be due to *DDL optimization*. I am not sure if exporting a table
that has been altered to add a default not null value (in 11gR1 and later)
will raise this kind of error. If you want to check if your column has not
been ''DDL optimized' then do this
select /*+ full(t1) */ distinct your_column from your_table t1;
select * from table(dbms_xplan.display_cursor(null, null, format =>
'ADVANCED'));
and look at the projection part to see if there is a NVL function applied
to your_column
Best regards
Mohamed Houri
www.hourim.wordpress.com
'
2014-04-15 21:31 GMT+02:00 Mark W. Farnham <mwf@xxxxxxxx>:
> And of course if you want to repair the situation in the source, something
> like
>
>
>
> update table set <column> = ‘ ‘ where <column> is null;
>
>
>
> would do the trick.
>
>
>
> (Where there is a single space between the apostrophes, but of course this
> does change your data. You would have to be sure it was a useful change.)
>
>
>
> Unless there are already single space values in this table that mean
> something different from the meaning of NULL, there is no change in
> meaning. Of course that does not mean it won’t make some poor program that
> currently works break.
>
>
>
> mwf
>
>
>
> *From:* oracle-l-bounce@xxxxxxxxxxxxx [mailto:
> oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *Mark W. Farnham
> *Sent:* Tuesday, April 15, 2014 2:04 PM
> *To:* oratune@xxxxxxxxx; howard.latham@xxxxxxxxx; giantpanda@xxxxxxx;
> 'ORACLE-L'
> *Subject:* 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 <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> 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
>
>
>
>
>
>
>
--
Bien Respectueusement
Mohamed Houri
Other related posts:
