Oracle 23c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS (DY)';
ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF';
CREATE TABLE table_name (id, data) AS
SELECT 1, EMPTY_CLOB() || (SELECT LISTAGG('PAR' || LPAD(LEVEL + 14, 6, '0'), ',') WITHIN GROUP (ORDER BY LEVEL)
                           FROM   DUAL
                           CONNECT BY LEVEL <= 40)
                       || ','
                       || (SELECT LISTAGG('PAR' || LPAD(LEVEL + 54, 6, '0'), ',') WITHIN GROUP (ORDER BY LEVEL)
                           FROM   DUAL
                           CONNECT BY LEVEL <= 40)
FROM   DUAL UNION ALL
SELECT 2, EMPTY_CLOB() || ',' FROM DUAL UNION ALL
SELECT 3, EMPTY_CLOB() FROM DUAL UNION ALL
SELECT 4, NULL FROM DUAL;
4 rows affected

CREATE OR REPLACE TYPE ARRAY AS TABLE OF VARCHAR2 (4000);
CREATE OR REPLACE FUNCTION PARSE_CSV (p_clob CLOB, p_separator VARCHAR2)
    RETURN ARRAY
    PIPELINED
AS
    v_size           NUMBER;
    v_start_pos      NUMBER := 1;
    v_new_position   NUMBER := 0;
    v_line           VARCHAR2 (4000);
    x_clob           CLOB := p_clob || TO_CLOB (p_separator);
BEGIN
    v_size := DBMS_LOB.getlength (x_clob);

    WHILE v_start_pos <= v_size
    LOOP
        v_new_position :=
            NVL (INSTR (x_clob, p_separator, v_start_pos), 4000);
        v_line := SUBSTR (x_clob, v_start_pos, v_new_position - v_start_pos);
        v_start_pos := v_new_position + LENGTH (p_separator);

        PIPE ROW (v_line);
    END LOOP;

    RETURN;
END;
/
SELECT t.id, d.column_value
FROM   table_name t
       CROSS APPLY TABLE(parse_csv(t.data, ',')) d


ID
COLUMN_VALUE


1
PAR000015

1
PAR000016

1
PAR000017

1
PAR000018

1
PAR000019

1
PAR000020

1
PAR000021

1
PAR000022

1
PAR000023

1
PAR000024

1
PAR000025

1
PAR000026

1
PAR000027

1
PAR000028

1
PAR000029

1
PAR000030

1
PAR000031

1
PAR000032

1
PAR000033

1
PAR000034

1
PAR000035

1
PAR000036

1
PAR000037

1
PAR000038

1
PAR000039

1
PAR000040

1
PAR000041

1
PAR000042

1
PAR000043

1
PAR000044

1
PAR000045

1
PAR000046

1
PAR000047

1
PAR000048

1
PAR000049

1
PAR000050

1
PAR000051

1
PAR000052

1
PAR000053

1
PAR000054

1
PAR000055

1
PAR000056

1
PAR000057

1
PAR000058

1
PAR000059

1
PAR000060

1
PAR000061

1
PAR000062

1
PAR000063

1
PAR000064

1
PAR000065

1
PAR000066

1
PAR000067

1
PAR000068

1
PAR000069

1
PAR000070

1
PAR000071

1
PAR000072

1
PAR000073

1
PAR000074

1
PAR000075

1
PAR000076

1
PAR000077

1
PAR000078

1
PAR000079

1
PAR000080

1
PAR000081

1
PAR000082

1
PAR000083

1
PAR000084

1
PAR000085

1
PAR000086

1
PAR000087

1
PAR000088

1
PAR000089

1
PAR000090

1
PAR000091

1
PAR000092

1
PAR000093

1
PAR000094

2
null

2
null

3
null

4
null


CREATE OR REPLACE FUNCTION parse_csv(
  i_str    IN  CLOB,
  i_delim  IN  VARCHAR2 DEFAULT ','
) RETURN array PIPELINED DETERMINISTIC
AS
  p_start        NUMBER(5) := 1;
  p_end          NUMBER(5);
  c_len CONSTANT NUMBER(5) := LENGTH( i_str );
  c_ld  CONSTANT NUMBER(5) := LENGTH( i_delim );
BEGIN
  IF c_len IS NULL THEN
    RETURN;
  END IF;

  p_end := INSTR( i_str, i_delim, p_start );
  WHILE p_end > 0 LOOP
    PIPE ROW (SUBSTR( i_str, p_start, p_end - p_start ));
    p_start := p_end + c_ld;
    p_end := INSTR( i_str, i_delim, p_start );
  END LOOP;
  IF p_start <= c_len + 1 THEN
      PIPE ROW (SUBSTR( i_str, p_start, c_len - p_start + 1 ));
  END IF;
EXCEPTION
  WHEN NO_DATA_NEEDED THEN
    NULL;
END;
/
SELECT t.id, d.column_value
FROM   table_name t
       CROSS APPLY TABLE(parse_csv(t.data, ',')) d


ID
COLUMN_VALUE


1
PAR000015

1
PAR000016

1
PAR000017

1
PAR000018

1
PAR000019

1
PAR000020

1
PAR000021

1
PAR000022

1
PAR000023

1
PAR000024

1
PAR000025

1
PAR000026

1
PAR000027

1
PAR000028

1
PAR000029

1
PAR000030

1
PAR000031

1
PAR000032

1
PAR000033

1
PAR000034

1
PAR000035

1
PAR000036

1
PAR000037

1
PAR000038

1
PAR000039

1
PAR000040

1
PAR000041

1
PAR000042

1
PAR000043

1
PAR000044

1
PAR000045

1
PAR000046

1
PAR000047

1
PAR000048

1
PAR000049

1
PAR000050

1
PAR000051

1
PAR000052

1
PAR000053

1
PAR000054

1
PAR000055

1
PAR000056

1
PAR000057

1
PAR000058

1
PAR000059

1
PAR000060

1
PAR000061

1
PAR000062

1
PAR000063

1
PAR000064

1
PAR000065

1
PAR000066

1
PAR000067

1
PAR000068

1
PAR000069

1
PAR000070

1
PAR000071

1
PAR000072

1
PAR000073

1
PAR000074

1
PAR000075

1
PAR000076

1
PAR000077

1
PAR000078

1
PAR000079

1
PAR000080

1
PAR000081

1
PAR000082

1
PAR000083

1
PAR000084

1
PAR000085

1
PAR000086

1
PAR000087

1
PAR000088

1
PAR000089

1
PAR000090

1
PAR000091

1
PAR000092

1
PAR000093

1
PAR000094

2
null

2
null

3
null

ID	COLUMN_VALUE
1	PAR000015
1	PAR000016
1	PAR000017
1	PAR000018
1	PAR000019
1	PAR000020
1	PAR000021
1	PAR000022
1	PAR000023
1	PAR000024
1	PAR000025
1	PAR000026
1	PAR000027
1	PAR000028
1	PAR000029
1	PAR000030
1	PAR000031
1	PAR000032
1	PAR000033
1	PAR000034
1	PAR000035
1	PAR000036
1	PAR000037
1	PAR000038
1	PAR000039
1	PAR000040
1	PAR000041
1	PAR000042
1	PAR000043
1	PAR000044
1	PAR000045
1	PAR000046
1	PAR000047
1	PAR000048
1	PAR000049
1	PAR000050
1	PAR000051
1	PAR000052
1	PAR000053
1	PAR000054
1	PAR000055
1	PAR000056
1	PAR000057
1	PAR000058
1	PAR000059
1	PAR000060
1	PAR000061
1	PAR000062
1	PAR000063
1	PAR000064
1	PAR000065
1	PAR000066
1	PAR000067
1	PAR000068
1	PAR000069
1	PAR000070
1	PAR000071
1	PAR000072
1	PAR000073
1	PAR000074
1	PAR000075
1	PAR000076
1	PAR000077
1	PAR000078
1	PAR000079
1	PAR000080
1	PAR000081
1	PAR000082
1	PAR000083
1	PAR000084
1	PAR000085
1	PAR000086
1	PAR000087
1	PAR000088
1	PAR000089
1	PAR000090
1	PAR000091
1	PAR000092
1	PAR000093
1	PAR000094
2	null
2	null
3	null
4	null