Re: NOT NULL constraint & value too large during import

Comments embedded.
astalavista wrote:
> Hi,
>
> I tranfered a schema from 10.1 to a 10.2 database
> I got 2 problems I did'nt understand:
>
> - how can I have NULL value with constraints valided ?

An initial 'alter constraint <constraint name> enable novalidate;' will enable the constraint, list it as valid, yet not check existing values in the table, some of which could be NULL. This is true even for a primary key constraint:

SQL> create table const_test(yarg number, yizzo number);

Table created.

SQL> insert into const_test values(null, 7)   2 /

1 row created.

SQL> / 1 row created.

SQL> / 1 row created.

SQL> / 1 row created.

SQL> / 1 row created.

SQL> alter table const_test
  2 add constraint const_test_pk
  3 primary key(yarg)
  4 novalidate;

Table altered.

SQL> A NOT NULL column contains NULL values, due to the novalidate directive when the constraint was created. No others may be added, but the existing values remain.

> - why some value are too large in the 10.2 database ( same charset
> AL32UTF8 ) the original table was created with VARCHAR2(3 CHAR)

That isn't possible, as the values being passed to the load operation are 4 characters in length:

 ORA-12899: value too large for column "TRESOMET"."PRIMECLI"."TYPCOU"  (actual: 4, maximum: 3)

I would check your original definition again.

>
> Thanks in advance ...
>

[rest of post snipped]
>
> Thierry CONSTANT
> SA & DBA
> NEXANS-CLICHY-FRANCE
> +33-1-55-62-79-72
> prestation3.exploitation_at_nexans.com

David Fitzjarrell Received on Mon Mar 27 2006 - 14:42:23 CST