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;

How to drop dependency constrations in Oracle for all tables