oracle reentrant alter table

Question

I have an SQL script to execute multiple times (must be reentrant).

One of the script line is

alter table MATABLE modify (MADATA null);

This commands works well when the 'not null' constraint for column MADATA of table MATABLE is effectively removed. But the second time, I got an error, example:

Erreur SQL : ORA-01451: colonne à modifier en non renseignée (NULL) ne peut être passée à NULL
01451. 00000 -  "column to be modified to NULL cannot be modified to NULL"

That's because the constraint was already removed by the first execution and no more exist.

How to execute this same script without error ? maybe through PL/SQL Script ?

Answer 1

You can use the data dictionary view user_tab_cols to check the table before doing the update.

declare
    lCount number;
begin

    select count(*) into lCount from user_tab_cols
    where table_name = 'MATABLE'
    and column_name = 'MADATA'
    and nullable = 'N';

    if (lCount = 1) then
        execute immediate 'alter table MATABLE modify (MADATA null)';
    end if;

end;

Note that user_tab_cols only contains the information about tables in the same schema as the logged on user. If you are modifying tables of another user, you can user all_tab_cols or dba_tab_cols.

Another option would be to use an exception handler and just throw away the exception, like this:

begin
    execute immediate 'alter table MATABLE modify (MADATA null)';
exception
    when others then
        null;
end;