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 what I need.
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;