Delete Cascade with Recursive PL/SQL

If you need to delete all rows in a table that has parent keys for other tables' foreign keys, and the foreign keys constraints have not been defined with “on delete cascade”, you can do a recursive delete with the following simple procedure.

This is typically something you will do only in a test or development database, and not in production. As always, it is a good thing to understand this procedure before you execute it:

create or replace procedure delete_cascade(  
  p_table_name in user_tables.table_name%type) is  
  l_pk user_constraints.constraint_name%type;  
begin  
  select constraint_name into l_pk  
  from user_constraints  
  where Constraint_Type='P'  
  and table_name=upper(p_table_name);  
  for c in (  
  select table_name  
  from user_constraints  
  where R_CONSTRAINT_NAME=l_pk) loop  
    delete_cascade(c.table_name);  
  end loop;  
  execute immediate 'delete from ' || upper(p_table_name);  
exception when no_data_found then -- Tables without constraint  
  execute immediate 'delete from ' || upper(p_table_name);;  
end;  
/