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;
/