Oracle – How to Get Meaningful Second String in ORA-02296 Error

alter-tablenulloracle

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.

Best Answer

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
Related Question