postgresql - How to convert code from Postgres to Oracle -

i have source table (t1):

id1 | id2 ---------- 1   | 2 1   | 5 4   | 7 7   | 8 9   | 1 

i want convert data (t2):

id1 | id2 | label ------------------ 1   | 2   | 1 1   | 5   | 1 4   | 7   | 2 7   | 8   | 2 9   | 1   | 1 

i found solution in postgresql:

with  recursive cte(id1, id2) (  select id1, id2, 1 level  t  union  select t.id1, cte.id2, cte.level + 1  t join       cte       on t.id2 = cte.id1 ) select id1, id2,    dense_rank() on (order grp) label (select id1, id2,          least(min(id2) on (partition id1), min(id1) on (partition id2))   grp,          level   cte  ) t level = 1; 

i want convert code oracle. how can convert code postgres oracle?

oracle 11.2 supports recursive ctes. deviates standard in recursive keyword not required (actually: must not used). if remove recursive keyword , definition of cte columns right following should work. need use different level reserved word either.

with cte (id1, id2, lvl) (  select id1, id2, 1 lvl  t  union  select t.id1, cte.id2, cte.lvl + 1  t     join cte on t.id2 = cte.id1 ) select id1,         id2,        dense_rank() on (order grp) label (   select id1,           id2,          least(min(id2) on (partition id1), min(id1) on (partition id2))   grp,          lvl   cte  ) t lvl = 1; 

here sqlfiddle example: http://sqlfiddle.com/#!4/deeb2/3

however doubt original query correct not have "starting condition" recursive cte. first part of union retrieves all rows of table. there should condition restrict "roots" of hierarchy unless i'm mis-understanding purpose of query.

a recursive cte can replaced connect by query, in case be:

select id1, id2, level lvl t connect prior id1 = id2; 

you can combine orginal query:

with cte (id1, id2, lvl) (   select id1, id2, level lvl   t   connect prior id1 = id2 ) select id1,         id2,        dense_rank() on (order grp) label (   select id1,           id2,          least(min(id2) on (partition id1), min(id1) on (partition id2))  grp,          lvl  cte ) t lvl = 1; 

although think should same, seems hierarchy traversed in different order. because recursive cte breadth first , connect by depth first recursion (or other way round).

sqlfiddle example second version: http://sqlfiddle.com/#!4/deeb2/4