I'm writing a PL-SQL script for my customer to modify the dataType of all columns with dataType CHAR.
So far so good. I've been able to write this.
1) I create a temp column
2) copy the values
3) drop the original column
4) make a new column (type and name)
5) copy all the values back
6) delete my temp column
the problems occur at point 3 ...
What if this column has a primary key constraint that has foreign keys pointing at them...
I can't drop neither the column nor the constraints due to the foreign keys...
I've tried if I could set the foreign keys disabled and I can but it doesn't seem to fix my problem.
Below, I've put my "disable foreign keys" script.
varOwner VARCHAR(4) := 'FLX0';
CURSOR foreignKeys IS
SELECT table_name, constraint_name, r_constraint_name FROM ALL_CONSTRAINTS
WHERE OWNER = varOwner AND table_name LIKE 'TB_FLX%' AND constraint_type = 'R';
FOR position IN foreignKeys
EXECUTE IMMEDIATE 'ALTER TABLE ' || position.table_name || ' DISABLE CONSTRAINT ' || position.constraint_name || '';
Can anyone please help. I've been staring at this code and have been trying different approaches all afternoon... Or is the only way to do this a manual one ? I'm surprised I couldn't find more info on the web about this topic