Skip to content
Advertisement

Oracle: How to get all possible constraint violations?

I want to know if data exists in the db that would get a constraint violation for all disabled constraints. (if the constraints were enabled)

I get all disabled constraints like this:

select * from all_constraints where STATUS='DISABLED';

Is there a solution?

Advertisement

Answer

I have found a simple solution for me:

spool 'disabled_constraint_violations.txt';
set serverout on size unlimited
DECLARE
    sql_enable varchar2(400);
    sql_disable varchar2(400);
BEGIN
   FOR c IN (select OWNER, TABLE_NAME, CONSTRAINT_NAME from all_constraints  where STATUS='DISABLED') LOOP
        BEGIN
        sql_enable := ' alter table ' ||  c.owner||'.'||c.table_name || ' enable constraint ' || c.constraint_name;
        execute immediate  sql_enable ;
        sql_disable := ' alter table ' ||  c.owner||'.'||c.table_name || ' disable constraint ' || c.constraint_name;
        execute immediate  sql_disable ;
        EXCEPTION   
            WHEN OTHERS THEN
                dbms_output.put_line('table: '||c.table_name||'    ||    constraint:  '||c.constraint_name||'    ||    error message:  '||sqlerrm);
        END;
   END LOOP;
END;
set serveroutput off
spool off
User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement