Use EXECUTE IMMEDIATE to disable constraint in loop

Question

FOR k IN (SELECT UC.CONSTRAINT_NAME, UC.TABLE_NAME FROM USER_CONSTRAINTS UC, TMP_DATA_MIG TDM
WHERE UC.TABLE_NAME = TDM.TABLE_NAMES AND UC.CONSTRAINT_TYPE IN('R','C','U')) LOOP
EXECUTE IMMEDIATE 'ALTER TABLE '||k.TABLE_NAME||' DISABLE CONSTRAINT '||k.CONSTRAINT_NAME||' CASCADE';
END LOOP;

Above FOR..IN loop join those table name in TMP_DATA_MIG with USER_CONSTRAINTS to get its corresponding constraint and disable them.

I am getting

 ORA-00911  "invalid character"

I tried to write it using bind variable

FOR k IN (SELECT UC.CONSTRAINT_NAME, UC.TABLE_NAME FROM USER_CONSTRAINTS UC, TMP_DATA_MIG_TABLE_LIST TDM WHERE UC.TABLE_NAME = TDM.TABLE_NAMES AND UC.CONSTRAINT_TYPE IN('R','C','U')) LOOP
l_sql := 'ALTER TABLE :TABLE_NAME DISABLE CONSTRAINT :CONSTRAINT_NAME CASCADE';  
 EXECUTE IMMEDIATE l_sql USING k.TABLE_NAME,k.CONSTRAINT_NAME;
 END LOOP;

The above code getting ORA-00903 "invalid table name"

How do I correct it in either one approach?

Answer 1

I found the root cause, one of the constraint name started as "_" hence detected as invalid character.

Answer 2

You can't use "using" in this case. Just concatenate the string and execute.

Now since this gives you an error message it's always usefull to print the string you are trying to execute and see what might cause the problem.

   for k in (select uc.constraint_name
                   ,uc.table_name
               from user_constraints        uc
                   ,tmp_data_mig_table_list tdm
              where uc.table_name = tdm.table_names
                and uc.constraint_type in ('R', 'C', 'U'))
   loop
      l_sql := 'ALTER TABLE '||k.table_name||'  DISABLE CONSTRAINT '||k.constraint_name||' CASCADE';
      dbms_output.put_line(l_sql);
      execute immediate l_sql;
   end loop;