Access path between parent and child tables in oracle

Question

When a table_name (T9) and a column_name (C1) is given, I need to find out nearest parent table which is having column_name (C1) in it.

For example: Parent table path for 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 is the parent child relationship starting from child table T9 (Read T9 is connected to T8 via C8 and so on)

Now I need to write a query which should return

T9->T8->T7  
T9->X8->X7
T9->Y8->Y7->Y6
T9->Z8->Z7->Z6->T5

I am trying to use all_constraints and all_cons_cols and connect by path to find the hierarchical relationship. Can someone help me if they have already created such query.

Answer 1

If you have a simple schema structure, this might work for you:

select access_path
from (
    SELECT substr(sys_connect_by_path(a.table_name, '->'),3) as access_path, column_name
    from all_cons_columns a
    left join all_constraints b
        on a.constraint_name = b.constraint_name
    start with a.table_name = :T9
    connect by nocycle prior b.constraint_name = b.r_constraint_name 
    order by level)
where column_name = :C1;

Modified from this similar question.