0

Whenever I run the following script*:

ALTER TABLE ANIMAL.MONKEY MODIFY TAIL_COUNT NOT NULL;

I receive the following error:

ORA-02296: cannot enable (ANIMAL.) - null values found

I have a script that modifies a large number of tables. It would be really helpful if the error message read:

ORA-02296: cannot enable (ANIMAL.MONKEY) - null values found

... or something else that identifies either the problem column or the table in which it resides.

ORA-02296 has the following detail about it:

ORA-02296 cannot enable (string.string) - null values found

Cause: Obvious

Action: an alter table enable constraint failed because the table contains values that do not satisfy the constraint.

This suggests there is a second string available somehow. How do I get the error to display more meaningful information?

*names have been changed to protect the innocent.

3

Provide a name for the constraint:

SQL> create table monkey(tail_count number);

Table created.

SQL> insert into monkey values (null);

1 row created.

SQL> alter table monkey modify tail_count not null;
alter table monkey modify tail_count not null
*
ERROR at line 1:
ORA-02296: cannot enable (ANIMAL.) - null values found

SQL> alter table monkey modify tail_count constraint tail_count_not_null not null;
alter table monkey modify tail_count constraint tail_count_not_null not null
                                                *
ERROR at line 1:
ORA-02296: cannot enable (ANIMAL.TAIL_COUNT_NOT_NULL) - null values found
0

I believe you could just use:

select * from ANIMAL.MONKEY where TAIL_COUNT is null;

to identify problematic rows, or add clause NOVALIDATE at the end of your alter statement.

  • How can it help in displaying error details ? – a1ex07 Jun 26 '15 at 14:35
  • It can't but it may help to pinpoint problematic rows, I don't know if there any more error details to be displayed here. – RLapinski Jun 26 '15 at 14:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.