drop index or constraint without knowing its name for Oracle
Your original version is mostly fine, except that you can't directly execute DDL in a PL/SQL block; rather, you have to wrap it in an EXECUTE IMMEDIATE:
execute immediate 'alter table MY_TABLE_NAME drop constraint "' || fName || '"';This would be true even if the constraint-name were known at compile-time, but it's doubly true in your case, since fName isn't the constraint-name, but rather, a variable containing the constraint-name.
Also, this:
if (fName != '') THENis not valid/meaningful, since in Oracle '' means NULL. You should write
IF fName IS NOT NULL THENinstead.
This is how to drop all constraints typed "R" for a column:
begin FOR rec IN (SELECT x.constraint_name fName FROM all_constraints x JOIN all_cons_columns c ON c.table_name = x.table_name AND c.constraint_name = x.constraint_name WHERE x.table_name = 'MY_TABLE_NAME' AND x.constraint_type = 'R' AND c.column_name ='MY_COLUMN_NAME') LOOP EXECUTE IMMEDIATE 'ALTER TABLE MY_TABLE_NAME DROP CONSTRAINT "' || rec.fName || '"'; END LOOP;end;