Reduce using loop for redundant SQL in PL SQL

Question

FOR k IN (SELECT UC.CONSTRAINT_NAME ,UC.TABLE_NAME FROM USER_CONSTRAINTS UC
   INNER JOIN MIG_TABLE_LIST MIG ON UC.TABLE_NAME = MIG.TABLE_NAME
   WHERE UC.TABLE_NAME = MIG.TABLE_NAME AND UC.CONSTRAINT_TYPE IN ('R', 'C', 'U')) LOOP
   l_sql := 'ALTER TABLE '||k.TABLE_NAME||' DISABLE CONSTRAINT '||k.CONSTRAINT_NAME||' CASCADE';
   EXECUTE IMMEDIATE l_sql;
END LOOP;

Above for..in loop, iterate table name in MIG_TABLE_LISTand disable all the corresponding constraints. The same loop is also used to enable constraint, how if I can get rid of using loop for constraint enabled?

Answer 1

I think for disable enabled you need to use something like

FOR k IN (SELECT UC.CONSTRAINT_NAME ,UC.TABLE_NAME FROM USER_CONSTRAINTS UC
   INNER JOIN MIG_TABLE_LIST MIG ON UC.TABLE_NAME = MIG.TABLE_NAME
   WHERE UC.TABLE_NAME = MIG.TABLE_NAME AND UC.CONSTRAINT_TYPE IN ('R', 'C', 'U') AND UC.STATUS='ENABLED') 
LOOP
   l_sql := 'ALTER TABLE '||k.TABLE_NAME||' DISABLE CONSTRAINT '||k.CONSTRAINT_NAME||' CASCADE';
   EXECUTE IMMEDIATE l_sql;
END LOOP;

and for enable disabled constraints

FOR k IN (SELECT UC.CONSTRAINT_NAME ,UC.TABLE_NAME FROM USER_CONSTRAINTS UC
   INNER JOIN MIG_TABLE_LIST MIG ON UC.TABLE_NAME = MIG.TABLE_NAME
   WHERE UC.TABLE_NAME = MIG.TABLE_NAME AND UC.CONSTRAINT_TYPE IN ('R', 'C', 'U') AND UC.STATUS='DISABLED') 
LOOP
   l_sql := 'ALTER TABLE '||k.TABLE_NAME||' ENABLE CONSTRAINT '||k.CONSTRAINT_NAME||' CASCADE';
   EXECUTE IMMEDIATE l_sql;
END LOOP;