How to drop dependency constrations in Oracle for all tables





3.00/5 (1 vote)
Background I had to write a script to do some tables alternation and data modification. I run into the problems where I had disable existing constraints and re enable them at the end of the process. I was searching the web for automated script to do that but whatever I found was not exactly w
I had to write a script to do some tables alternation and data
modification. I run into the problems where I had disable existing
constraints and re enable them at the end of the process. I was
searching the web for automated script to do that but whatever I found
was not exactly what I need. Background
Using the code
This is strait forward script execution. I have commented EXECUTE IMMEDIATE statement because it was giving me the error I still can not resolve, but if I output this to the console and manually run it works like a charm.
Pleases post a comment if you find why it does not EXECUTE IMMEDIATE statement properly.
SET SERVEROUTPUT ON DECLARE CURSOR cur_UserTables IS SELECT table_name FROM user_tables; rec_Tables cur_UserTables%ROWTYPE; dynamicSql VARCHAR2(4000); tbl_Name VARCHAR(200); BEGIN OPEN cur_UserTables; LOOP FETCH cur_UserTables INTO rec_Tables; -- Get the cursor in the record variable. EXIT WHEN cur_UserTables%NOTFOUND; -- Exit the loop when no more rows are found. BEGIN DECLARE CURSOR cur_depTbl IS SELECT c.table_name, c.constraint_name FROM user_constraints p JOIN user_constraints c ON(p.constraint_name = c.r_constraint_name) WHERE (p.constraint_type = 'P' OR p.constraint_type = 'U') AND c.constraint_type = 'R' AND p.table_name = rec_Tables.table_name; rec_depTab cur_depTbl%ROWTYPE; BEGIN OPEN cur_depTbl; LOOP FETCH cur_depTbl INTO rec_depTab; EXIT WHEN cur_depTbl%NOTFOUND; begin dynamicSql:= ' alter table ' || rec_depTab.table_name || ' disable constraint ' || rec_depTab.constraint_name || ' cascade;'; dbms_output.put_line(dynamicSql); --EXECUTE IMMEDIATE (dynamicSql); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('ERROR: Re run manually: ' || dynamicSql); end; COMMIT; END LOOP; CLOSE cur_depTbl; END; END ; END LOOP; CLOSE cur_UserTables; END;