title: Full path traversed for each ID with cycle Oracle
body: <p>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</p>
  
  <p>input</p>
  
  <pre>
  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
  </pre>
  
  <p>output should be the path of current node like -</p>
  
  <pre>
  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
  </pre>
  
  <p>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.<br>
  The error that i sometimes encounter with below code is "result of string concatenation is too long"</p>
  
  <p>my code </p>
  
  <pre><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,
          '&lt;' || listagg(n, '|&lt;') 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, '&lt;') list FROM t_br WHERE cnt &gt; 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, '^(&lt;(' || used || '0)\|)*(&lt;(\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;
  </code></pre>
  
  <p>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 </p>