How to get the second string in the ORA-02296 error to show something meaningful

Question

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.

Answer 1

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

Answer 2

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.