Comma separated string

Using Analytic functions  - 25-Oct-18

pass string as  '1,2,4,5,678'

SELECT REGEXP_SUBSTR (:i_string, '[^,]+', 1, level)
      FROM dual
      CONNECT BY LEVEL <= LENGTH(regexp_replace(:i_string,'[^,]*'))+1;

---Below is longer route


Below code can be used to separate trx numbers from comma separated sting..

Definitely re-usable ...

set serveroutput on
DECLARE
  l_list  VARCHAR2(240) := '8345825,9999003,8233347';
  l_trx VARCHAR2(50);
 
  l_start number;
  l_len number;
  l_cnt number;
 
BEGIN
l_cnt := length(l_list) - length(replace(l_list, ',',''));

dbms_output.put_line(' Cnt '||l_cnt);

l_start:=0;  -- start from 0


for i in 1..l_cnt+1
loop

--l_len:= INSTR(l_list,',',1,i) - decode(l_start,0,1,l_start) ;

select INSTR(l_list,',',1,i) - decode(l_start,0,1,l_start) into l_len from dual;

if l_len < 0 then l_len:= 100; end if; -- for last trx

dbms_output.put_line( i ||'**St '||l_start);
dbms_output.put_line( i ||'**Ln '||l_len);

 l_trx := SUBSTR(l_list,l_start,l_len);

 dbms_output.put_line( i ||'    ' ||l_trx );

 l_start := INSTR(l_list,',',1,i)+1;
end loop;

end;
/