[SOLVED] Use EXECUTE IMMEDIATE to disable constraint in loop

Use EXECUTE IMMEDIATE to disable constraint in loop

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?

Solution

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