Recusive delete in oracle

With below procedure we can delete dependent row from table ( either parent or child table ), with enable disable of constraints.

It is one of the type of explicit delete cascade procedure for oracle.

drop table to_be_deleted purge;
create table to_be_deleted
(tname varchar2(30)       -- table name
,rid rowid                -- rowid
,lvl number               -- level: 1=parent, 2=child, 3=grandchild, etc.
,seq_ins number           -- sequence order of record inserted
,flg_del char             -- flag deleted: Y=record deleted
,seq_del number           -- global order of record deletion
);
 

 set serveroutput on size 1000000

 
 create or replace procedure prc_delete_cascade
(p_tname varchar2  -- table name
,p_where varchar2  -- where clause identifying records to be cascade deleted
)
is
  dummy         char;
  v_sqlcode     number;
  v_sqlerrm     varchar2(32767);
  v_param_val   integer := 0;
  v_sql         varchar2(4000);
  v_ret_cde     number;
  e_bad_params  exception;
  v_iter        number;
  v_plvl        number;
  v_seq_del     number;
  v_max_iter    number := 1000000000;
  
  function delete_bott_row
  return number
  is
    v_sql        varchar2(4000);
    v_ptname     varchar2(30);  -- parent table name
    v_ppkname    varchar2(30);  -- parent primary key constraint name
    v_ppkcname   varchar2(30);  -- parnet primary key column name
  v_prowid      rowid;
    v_crowid      rowid;
  v_ctname     varchar2(30);  -- child table name
    v_cfkname    varchar2(30);  -- child foreign key constraint name
    v_cfkcname   varchar2(30);  -- child foreign key column name
  v_ins        number;
    v_seq_ins    number;
  v_sqlerrm    varchar2(4000);
    v_sqlcode    number;
    
    e_const_viol exception;
    pragma exception_init(e_const_viol, -2292);
  e_max_iter_reached exception;
    
  begin
    v_iter := v_iter + 1;
     
    if v_iter >= v_max_iter then
      raise e_max_iter_reached;
    end if;
     
    dbms_output.put_line('- Iter '||to_char(v_iter));
    dbms_output.put_line('----------');
    dbms_output.put_line('- Starting function delete_bott_row');
    v_sql := 'select tname, rid, lvl, seq_ins from (select * 
    from to_be_deleted where flg_del = ''N'' order by lvl desc, seq_ins desc) where rownum=1';
    --  dbms_output.put_line('- SQL: '||v_sql);
    execute immediate v_sql into v_ptname, v_prowid, v_plvl, v_seq_ins;
     
    dbms_output.put_line('- Selected row: table name: '||v_ptname||', level: '||v_plvl||', seq: '||v_seq_ins);
     
    v_sql := 'delete from '||v_ptname||' where rowid='''||v_prowid||'''';
    dbms_output.put_line('- SQL: '||v_sql);
    execute immediate v_sql;
    dbms_output.put_line('- Row deleted !!!');
    v_ret_cde := 1;
    v_seq_del := v_seq_del + 1;
     
    dbms_output.put_line('- Mark the row deleted');
    v_sql := 'update to_be_deleted set flg_del = ''Y'', seq_del = '||to_char(v_seq_del)
    ||' where tname='''||v_ptname||''' and rid='''||v_prowid||'''';
    -- dbms_output.put_line('- SQL: '||v_sql);
    execute immediate v_sql;
    -- dbms_output.put_line('- Updated table to_be_deleted, row marked deleted');
    -- dbms_output.put_line('- End of iter '||to_char(v_iter));
    dbms_output.put_line('----------');
      
    -- call function delete_bott_row recursively
    v_ret_cde := delete_bott_row;
 
    return 0;
      
  exception
    when no_data_found then
      dbms_output.put_line('- Table to_be_deleted is empty, delete cascade has completed successfully.');
      v_ret_cde := 0;
      return 0;
       
    when e_const_viol then
      v_sqlcode := SQLCODE;
      v_sqlerrm := SQLERRM;
 
      v_ret_cde := v_sqlcode;
        
      dbms_output.put_line('>Constraint Violation. Record has children');
      -- dbms_output.put_line('Error code: '||to_char(v_sqlcode));
      v_cfkname := substr(v_sqlerrm,instr(v_sqlerrm,'.')+1,instr(v_sqlerrm,')') - instr(v_sqlerrm,'.')-1);
       
      dbms_output.put_line('>Child FK name: '||v_cfkname);
       
      select table_name, column_name
        into v_ctname, v_cfkcname
        from user_cons_columns
       where constraint_name=v_cfkname;
      dbms_output.put_line('>Child table name: '||v_ctname||'. FK column name: '|| v_cfkcname);
       
      select constraint_name, column_name
        into v_ppkname, v_ppkcname 
        from user_cons_columns
       where constraint_name = (select r_constraint_name 
                                  from user_constraints 
                                  where constraint_name=v_cfkname);
      dbms_output.put_line('>Parent PK/UK name: '||v_ppkname||'. Parent PK/UK column: '||v_ppkcname);
                                
      v_sql := 'insert into to_be_deleted(tname, rid, lvl, seq_ins, flg_del) '||
               'select '''||v_ctname||''', rowid, '||to_char(v_plvl+1)||', rownum, ''N'' '||
               'from '||v_ctname||' '||
               'where '||v_cfkcname||' =any (select '||v_ppkcname||' from '||v_ptname||
       ' where rowid =any (select rid from to_be_deleted where tname = '''||v_ptname||'''))';
      -- dbms_output.put_line('- SQL: '||v_sql);
      execute immediate v_sql;
 
      select count(*) 
        into v_ins
        from to_be_deleted 
       where lvl = v_plvl+1 
         and tname = v_ctname
         and flg_del = 'N';
      dbms_output.put_line('>Found '||to_char(v_ins)||' child records which were added to table to_be_deleted');   
         
      v_ret_cde := delete_bott_row;
       
      return  v_ret_cde;
      
    when e_max_iter_reached then
      dbms_output.put_line('Maximum iterations reached.  Terminating procedure.');
      raise;
        
    when others then
      raise;
           
  end delete_bott_row;
          
begin
  dbms_output.put_line('Beginning');
  dbms_output.put_line('================================');
 
  -- validate p_table
  begin
    select 'Y'
      into dummy
      from user_tables
     where table_name=upper(p_tname);
  exception
    when no_data_found then
    v_param_val := 1;
    dbms_output.put_line('Table '||p_tname||' does not exist.');
    raise e_bad_params;
  end;
  dbms_output.put_line('- Parameter p_tname validated');
 
  -- validate p_where 
  begin
    execute immediate 'select ''Y'' from '||p_tname||' where '||p_where INTO dummy;
  exception
    when no_data_found then  -- where clause returns no records
      dbms_output.put_line('Record(s) not found.  Check your where clause parameter');
      v_param_val := 2;
      raise e_bad_params;
    when too_many_rows then  -- found multiple records means it is ok
      null;  
    when others then  --  any other records means where clause has something wrong.
      dbms_output.put_line('Where clause is malformed');      
      v_param_val := 2;
      raise e_bad_params;
  end;    
  dbms_output.put_line('- Parameter p_where validated');
 
  if v_param_val > 0 then raise e_bad_params; end if;
  
  v_iter := 0;
  v_plvl := 1;
  v_seq_del := 0;
  v_sql := 'insert into to_be_deleted(tname, rid, lvl, seq_ins, flg_del) select '''||
  upper(p_tname)||''', rowid, '||to_char(v_plvl)||', rownum, ''N'' from '||p_tname||' where '||p_where;
 
  dbms_output.put_line('- Inserting initial record');
  dbms_output.put_line('- SQL: '||v_sql);
  execute immediate v_sql;
  dbms_output.put_line('- Record(s) inserted'); 
  
  
  dbms_output.put_line('- Calling function delete_bott_row to delete last row of table to_be_deleted');               
  dbms_output.put_line('-----------------------------------');               
  v_ret_cde :=  delete_bott_row; 
  -- dbms_output.put_line('- Back from function delete_bott_row');               
  -- dbms_output.put_line('Return code: '||to_char(v_ret_cde));               
  dbms_output.put_line('- End of procedure');               
 
exception
  when e_bad_params then
    dbms_output.put_line('Bad parameters, exiting.');
end;
/ 



exec prc_delete_cascade('xent','xent_id between 1669 and 1670')
or
exec prc_delete_cascade('table_name','column_name = 1669');

Source:- https://community.oracle.com/thread/2196578?tstart=0