sql - Access path between parent and child tables in oracle -

when table_name (t9) , column_name (c1) given, need find out nearest parent table having column_name (c1) in it.

for example: parent table path t9

t9(c9,c8) -> t8(c8,c7) -> t7(c7,c1) -> t6(c1,c2) -> t5(c1,c3) t9(c9,c11) -> x8(c11,c7) -> x7(c7,c1) -> t6(c1,c2) -> t5(c1,c3) t9(c9,c12) -> y8(c12,c7) -> y7(c7,c3) -> y6(c3,c1) -> t5(c1,c3) t9(c9,c13) -> z8(c13,c7) -> z7(c7,c2) -> z6(c2,c3) -> t5(c3,c1) 

above parent child relationship starting child table t9 (read t9 connected t8 via c8 , on)

now need write query should return

t9->t8->t7   t9->x8->x7 t9->y8->y7->y6 t9->z8->z7->z6->t5 

i trying use all_constraints , all_cons_cols , connect path find hierarchical relationship. can me if have created such query.

if have simple schema structure, might work you:

select access_path (     select substr(sys_connect_by_path(a.table_name, '->'),3) access_path, column_name     all_cons_columns     left join all_constraints b         on a.constraint_name = b.constraint_name     start a.table_name = :t9     connect nocycle prior b.constraint_name = b.r_constraint_name      order level) column_name = :c1; 

modified this similar question.