Change all foreign keys in oracle database, add on delete cascade option

You have an Oracle database, and all foreign keys are created with ON DELETE NO ACTION.
Now you want to switch them with ON DELETE CASCADE.
This situation could happen for example because your tables are created through JPA or something like that. Here is the solution.



After a lot of search, the only way I found is to delete and recreate all foreign keys in order to specify On Delete Cascade option.
And there is also no way with Java Persistence Api (JPA) to create ManyToOne relations with the delete cascade option.

So, here is a script I found in the web and modified just a little, that drops all foreign keys and recreates them with the "on delete cascade" option.
You have to copy all the text generated from the DBMS Output and execute it manually as script (after cheking it carefully for security),

DECLARE
textadd clob;
CURSOR consCols (theCons VARCHAR2, theOwner VARCHAR2) IS
 select * from user_cons_columns
 where constraint_name = theCons and owner = theOwner
 order by position;
firstCol BOOLEAN := TRUE;

begin
 textadd := '';

 -- For each constraint
 FOR cons IN (select * from user_constraints
  where delete_rule = 'NO ACTION'
  and constraint_name not like '%MODIFIED_BY_FK'    -- these constraints we do not want delete cascade
  and constraint_name not like '%CREATED_BY_FK'
  order by table_name)
 LOOP
   
  -- Drop the constraint
  DBMS_OUTPUT.PUT_LINE( 'ALTER TABLE ' || cons.OWNER || '.' || cons.TABLE_NAME || ' DROP CONSTRAINT ' || cons.CONSTRAINT_NAME || ';');
  -- Re-create the constraint
  textadd := textadd || 'ALTER TABLE ' || cons.OWNER || '.' || cons.TABLE_NAME || ' ADD CONSTRAINT ' || cons.CONSTRAINT_NAME || ' FOREIGN KEY (';
  firstCol := TRUE;
  -- For each referencing column
  FOR consCol IN consCols(cons.CONSTRAINT_NAME , cons .OWNER )
  LOOP
   IF(firstCol) THEN
    firstCol := FALSE;
   ELSE
    textadd := textadd || ',';
   END IF;
   textadd := textadd || consCol .COLUMN_NAME ;
  END LOOP;                                    
  textadd := textadd || ') REFERENCES ';

  firstCol := TRUE;
  -- For each referenced column
  FOR consCol IN consCols(cons.R_CONSTRAINT_NAME , cons.R_OWNER)
  LOOP
   IF(firstCol) THEN                   
    textadd := textadd || consCol .OWNER || '.' || consCol.TABLE_NAME || ' (' ;                   
    firstCol := FALSE;
   ELSE
    textadd := textadd || ',';
   END IF;
   textadd := textadd || consCol .COLUMN_NAME ;
  END LOOP;                                    

  textadd := textadd || ')  ON DELETE CASCADE  ENABLE VALIDATE;';
 END LOOP;
   
 DBMS_OUTPUT.PUT_LINE( '-- ----------------------- --' );
 DBMS_OUTPUT.PUT_LINE( '--  FOREIGN KEYS CREATION  --' );
 DBMS_OUTPUT.PUT_LINE( '-- ----------------------- --' );
 DBMS_OUTPUT.PUT_LINE( textadd);
end;