SQL & PL/SQL :: Update Hierarchical Relation

I have a table which contains the connectivity details. It is telecom data. The table contains the relation between the point features(Ex: Terminal) and linear features(Ex: Cable)

Below is some sample data

drop table connectivity ;
create table connectivity
(
ftr_no number(5),
ftr_id number(5),

[Code]...

-- 2nd linear ftr

insert into connectivity values (30,10359,33, 9655, 2,NULL,NULL);
insert into connectivity values (30,10359,33, 9656, 1,NULL,NULL);
insert into connectivity values (30,10359,30, 10359,3,NULL,NULL);

[Code]...

In the connectivity table PARENT_FTR_NO and PARENT_FTR_ID needs to be updated based on these conditions.

1. Correspponding to each feature there are 3 entries in this table.

2. Each feature(Point orLinear) has 2 sides INcoming and OUTgoing

3. The possible values for the RELATION column are 1, 2 and 3 where

-> 1 indicates feature conncted at the OUTgoing end
->2 indicates features connected at the INcoming end
->3 indicates self connected

4. Always a linear feature is the parent of a point feature which are connected to the OUTgoing end of the linear feature (i.e where RELATION = 1)

There is another table which contains FEATURE_NO and FEATURE_TYPE.

create table ftr_catgry
(
ftr_no number(5),
ftr_type char(1)
);

[Code]...

FTR_NO F
---------- -
33 P
31 L
30 L

Where
'L' represents linear feature
'P' represents point feature

The relation should be updated as

Step 1. Start with a linear feature, for example FTR_NO = 31

SQL> select * from connectivity where ftr_no = 31 and ftr_id = 10354 and relation = 1;

FTR_NO FTR_ID CONNECTED_FTR_NO CONNECTED_FTR_ID RELATION PARENT_FTR_NO PARENT_FTR_ID
---------- ---------- ---------------- ---------------- ---------- ------------- -------------
31 10354 33 9651 1

The feature connected to the OUTend of the 10354 i 9651 with FTR_NO = 33 and it is a point feature so the parent for 9651 is 10354

Step 2.

SQL> select * from connectivity where ftr_no = 33 and ftr_id = 9651 and relation = 1;

FTR_NO FTR_ID CONNECTED_FTR_NO CONNECTED_FTR_ID RELATION PARENT_FTR_NO PARENT_FTR_ID
---------- ---------- ---------------- ---------------- ---------- ------------- -------------
33 9651 33 9652 1

9652 is connected to 9651 at the OUTend, since 9651 is point feature so the parent of 9651 which is a linear feature is also the parent for 9652

Likewise the all relations have to be updated

Expected result after update

SQL> select * from connectivity;

FTR_NO FTR_ID CONNECTED_FTR_NO CONNECTED_FTR_ID RELATION PARENT_FTR_NO PARENT_FTR_ID
---------- ---------- ---------------- ---------------- ---------- ------------- -------------
31 10354 33 9650 2
31 10354 33 9651 13110354
31 10354 33 10354 3
33 9651 31 10354 2
33 9651 33 9652 13110354
33 9651 33 9651 3
33 9652 33 9651 2

[Code]...

24 rows selected.