How to delete data in all child tables including parent table.

See more:

Oracle

I have few tables having constraint relationship with foreign keys like below:

1.parent_table id(primary key)
2.parent_child c_id(primary key) and id(reference from parent_table)
3.child ch_id(primary key) and c_id(reference from parent_child table)

Now i need to delete a record from parent_table including with child tables which are having same record. But here i am getting challenge is parent_table references parent_child table but parent_child table references child table with another column.

How can i identify exact value from child tables to delete that particular record.

Thanks in advance.

What I have tried:

create table parent(id number primary key,name varchar2(30));

create table parent_child(c_id number primary key,id number,name varchar2(30));

ALTER TABLE parent_child ADD
    CONSTRAINT parent_child_fk FOREIGN KEY (id)
       REFERENCES parent(id);
       
create table child(ch_id number primary key,c_id number,name varchar2(30));

ALTER TABLE child ADD
    CONSTRAINT child_fk FOREIGN KEY (c_id)
       REFERENCES parent_child(c_id);


I have tried below query to find the tables:

<pre>
SELECT DISTINCT LEVEL,  PT AS "TNAME",COLUMN_NAME
            FROM (SELECT A.OWNER W1,
                         A.table_name PT,
                         A.constraint_name C1,
                         A.r_constraint_name R1,
                         B.OWNER W2,
                         B.table_name CT,
                         B.constraint_name C2,
                         B.r_constraint_name R2,
                         C.COLUMN_NAME
                    FROM ALL_CONSTRAINTS A, all_constraints B,ALL_CONS_COLUMNS C
                   WHERE     (A.constraint_name = b.r_constraint_name(+))
                   AND A.CONSTRAINT_NAME=C.CONSTRAINT_NAME
                         AND A.OWNER = UPPER ('USER')
                         AND B.OWNER(+) = UPPER ('USER') 
                         AND A.r_constraint_name IS NULL
                         AND A.constraint_type IN ('P', 'R')) V1
      START WITH PT = UPPER ('PARENT') 
      CONNECT BY PRIOR CT = PT AND LEVEL <= 10 ORDER BY LEVEL

:: This query returning only column names.. I would like to put this query in the implicit cursor and find the values with parent table column value.