Oracle: delete a row and all rows preventing it to be deleted because of "child record found" errors.

fk_delete_recursive.sql

DECLARE
  -- ORA-02292: integrity constraint (OWNER.FK) violated - child record found
  ora_02292 EXCEPTION;
  PRAGMA EXCEPTION_INIT(ora_02292, -2292);

  l_foreign_key VARCHAR2(400);

  PROCEDURE delete_row(pi_table  IN VARCHAR2,
                       pi_column IN VARCHAR2,
                       pi_value  IN VARCHAR2) IS
    l_new_table         all_cons_columns.table_name%TYPE;
    l_new_column        all_cons_columns.column_name%TYPE;
    l_new_value         VARCHAR2(400);
    l_referenced_column all_cons_columns.column_name%TYPE;
  BEGIN
    dbms_output.put_line('DELETE FROM ' || pi_table || ' WHERE ' || pi_column || ' = ' || pi_value);
    EXECUTE IMMEDIATE 'DELETE FROM ' || pi_table || ' WHERE ' || pi_column || ' = ' || pi_value;
  EXCEPTION
    WHEN ora_02292 THEN
      IF REGEXP_LIKE(SQLERRM, '^ORA-02292: integrity constraint (.+) violated - child record found$')
      THEN
        -- retrieve foreign key name        
        l_foreign_key := regexp_replace(SQLERRM, '.*\((.+)\).*', '\1');
      
        -- retrieve table & column name from which we must remove the child record
        SELECT table_name, column_name
          INTO l_new_table, l_new_column
          FROM all_cons_columns
         WHERE owner || '.' || constraint_name = l_foreign_key;
      
        -- retrieve column of pi_table which is referenced by the foreign key
        SELECT c.column_name
          INTO l_referenced_column
          FROM all_cons_columns c, all_constraints a
         WHERE c.constraint_name = a.r_constraint_name
           AND a.owner || '.' || a.constraint_name = l_foreign_key;
      
        EXECUTE IMMEDIATE 'SELECT ' || l_referenced_column || ' FROM ' || pi_table || ' WHERE ' || pi_column || ' = ' ||
                          pi_value
          INTO l_new_value;
      
        dbms_output.put_line('  Found foreign key: ' || l_foreign_key);
      
        -- recursively delete children 
        delete_row(l_new_table, l_new_column, l_new_value);
      
        -- delete current row
        delete_row(pi_table, pi_column, pi_value);
      END IF;
  END delete_row;
BEGIN
  delete_row('table_name', 'column_name', 'value');
END;