Full path traversed for each ID with cycle Oracle

Question

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.
The error that i sometimes encounter with below code is "result of string concatenation is too long"

my code

create or replace procedure pathing 
as 
  type varr is table of varchar(4000);
  visit varr;

  t number;
  --v varchar2(40);
  fp varchar2(1000);
  np varchar2(1000);

  type stype is  record(fp varchar2(1000),np varchar2(1000),t number);
  type sinput is table of stype;
  iarray sinput;

begin

  select id  
  bulk collect into visit 
  from table_source 
  group by id 
  order by count(1) desc;

  delete from table_final;
  commit;

  for k in visit.first .. visit.last loop

    delete from table_temp;
    commit;

    insert into table_temp
    select distinct prev_pg, page_id, next_pg, visit(k)  
    from table_source  
    where visit_id = visit(k) 
    order by prev_pg desc;

    commit;

    insert into table_final 
    WITH t_n AS ( 
      SELECT prev_pg, page_id, next_pg, rownum n FROM table_temp
    ),
    t_br AS (
      SELECT 
        prev_pg,
        page_id,
        '<' || listagg(n, '|<') within GROUP(ORDER BY n) || '|' br,
        COUNT(0) cnt
    FROM 
      t_n
    GROUP BY 
      prev_pg, page_id
    ),
    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, page_id, next_pg, used) AS ( 
        SELECT 1, page_id, next_pg, '' 
        FROM t_n 
        WHERE prev_pg is null
      UNION ALL
        SELECT 
         step + 1,
         t_br.page_id,
         t_n.next_pg,
         CASE
           WHEN instr(list, '|' || n || '|') = 0
            THEN used
            ELSE used || n || '|'
          END
       FROM 
         t_mp,
         t_path
         JOIN t_br
           ON next_pg = t_br.page_id AND t_path.page_id = prev_pg
         JOIN t_n
           ON n = regexp_substr(br, '^(<(' || used || '0)\|)*(<(\d+))?', 1, 1, '', 4)
    ) cycle step
    SET is_cycle TO 'Y' DEFAULT 'N'
    SELECT 
      page_id,
      next_pg,
      step,
      visit(k)  
    FROM t_path 
    ORDER BY 1;

    commit;

  end loop;
end;

Explaining my example further more:- I want full path journey of each ID , in the example i have taken ID 1 as example. For ID 1 we have a set of current, previous and next value. So we need to find the path using these values . For example for id 1 the path starts with 'a' because prev column is empty. then we see the next value of a is b i.e current is a and next is b so we search in all the rows of id 1 for prev value as a and current value as b , at the point we find the same we take the next value of the row and repeat the process. For example here prev a ,current b and next is c so we again search for prev b and current c and so on until we get the full path until we encounter next as null as that would be the last

Answer 1

The solution through Hierarchical query clauses seems to be tricky, but there should be a solution, still as an alternative, use your existing PL/SQL code but change the VARCHAR field to CLOB to avoid "result of string concatenation is too long".

Answer 2

you may use the following query in your procedure to accomplish your goal

select id,current into v_your_output_collection   from nodes 
where id = :vId
start with prev_node is null 
connect by 
NOCYCLE
prior next_node= current and  prior current = prev_node

that works in Oracle 10g and up