# Full path between two nodes cyclic structure pl/sql

I have an input data which consists of ID,prev,current and next node (not sorted). I have to find a path between the first and last page for each ID which covers all the nodes traversed. for eg : if my input data is like : first column is ID, second column is prev_node, third column is current node, fourth column is next node. Prev_node will be empty for starting value and next node will be empty for last value

*input*

id prev current next ------------------------ 1 a b c 1 a e f 1 a b g 1 a b o 1 b c d 1 b g h 1 b o p 1 c d a 1 c b g 1 d a e 1 e f e 1 e f f 1 f e f 1 f f f 1 f f a 1 f a b 1 g h i 1 h i j 1 h j i 1 i j i 1 i i k 1 i k l 1 j i i 1 k l m 1 l m n 1 l n a 1 m n a 1 n a b 1 o p q 1 p q r 1 q r s 1 r s t 1 s t u 1 t u v 1 u v w 1 v w x 1 w x 1 a b

*output* should be the path of current node like -

ID current ------------- 1 a 1 b 1 c 1 d 1 a 1 e 1 f 1 e 1 f 1 f 1 f 1 a 1 b 1 b 1 g 1 h 1 i 1 j 1 j 1 i 1 i 1 k 1 l 1 m 1 n 1 n 1 a 1 b 1 o 1 p 1 q 1 r 1 s 1 t 1 u 1 v 1 w 1 x

There will be many IDs with similar data here. I have shown only one ID(1). Also here i have used alphabets which will actually be 200-500 character long string. I tried the SQL approach with little modification, it works fine if an ID has 100 or below rows but gives string concatenation error for more rows (even after converting the long strings to number). Can anyone please suggest a robust procedure based approach to same. I tried some but it doesn't work for more than 300 rows for a ID.

## Answers

with t_n as ( select prev, curr, next, rownum n from your_table ), t_br as ( select prev, curr, '<'||listagg(n, '|<')within group(order by n)||'|' br, count(0) cnt from t_n group by prev, curr ), t_mp as ( select '|'||listagg(list)within group(order by null) list from ( select replace(br, '<') list from t_br where cnt > 1 ) ), t_path(step, curr, next, used) as ( select 1, curr, next, '' from t_n where prev is null union all select step + 1, t_br.curr, t_n.next, case when instr(list, '|'||n||'|') = 0 then used else used||n||'|' end from t_mp, t_path join t_br on next = t_br.curr and t_path.curr = prev join t_n on n = regexp_substr(br,'^(<('||used||'0)\|)*(<(\d+))?',1,1,'',4) ) cycle step set is_cycle to 'Y' default 'N' select step, curr from t_path order by 1;