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;
/

– version:

SCOTT@orcl_12.1.0.2.0> SELECT banner FROM v$version
 2  /
BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE    12.1.0.2.0    Production
TNS for 64-bit Windows: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production
5 rows selected.
– type and function:

SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE TYPE ARRAY AS TABLE OF VARCHAR2 (4000);
 2  /
Type created.
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE FUNCTION PARSE_CSV
 2    (p_clob         CLOB,
 3      p_separator  VARCHAR2)
 4    RETURN ARRAY
 5    PIPELINED
 6  AS
 7    v_size           NUMBER;
 8    v_start_pos     NUMBER := 1;
 9    v_new_position  NUMBER := 0;
10    v_line           VARCHAR2 (4000);
11    x_clob           CLOB := p_clob || TO_CLOB (p_separator);
12  BEGIN
13    v_size := DBMS_LOB.getlength (x_clob);
14    WHILE v_start_pos <= v_size
15    LOOP
16       v_new_position := NVL (INSTR (x_clob, p_separator, v_start_pos), 4000);
17       v_line := SUBSTR (x_clob, v_start_pos, v_new_position - v_start_pos);
18       v_start_pos := v_new_position + LENGTH (p_separator);
19       PIPE ROW (v_line);
20    END LOOP;
21    RETURN;
22  END PARSE_CSV;
23  /
Function created.
SCOTT@orcl_12.1.0.2.0> SHOW ERRORS
No errors.
– creation and population of variable:

SCOTT@orcl_12.1.0.2.0> VARIABLE g_clob CLOB
SCOTT@orcl_12.1.0.2.0> DECLARE
 2    v_clob  CLOB  := EMPTY_CLOB();
 3  BEGIN
 4    FOR i IN 1 .. 2247 LOOP
 5       v_clob := v_clob || 'PAR' || LPAD (i + 14, 6, '0') || ',';
 6    END LOOP;
 7    :g_clob := RTRIM (v_clob, ',');
 8  END;
 9  /
PL/SQL procedure successfully completed.
 
– select statement passing variable to function:

SCOTT@orcl_12.1.0.2.0> SELECT * FROM TABLE (parse_csv (:g_clob, ',') )
 2  /
COLUMN_VALUE
--------------------------------------------------------------------------------
PAR000015
PAR000016
PAR000017
PAR000018
PAR000019
PAR000020
PAR000021
PAR000022
PAR000023
PAR000024
PAR000025
– truncated to save space on forum
PAR002256
PAR002257
PAR002258
PAR002259
PAR002260
PAR002261
2247 rows selected.

CREATE OR REPLACE TYPE PARRAY as table of CLOB;

CREATE OR REPLACE FUNCTION parse_csv(
  i_str    IN  CLOB,
  i_delim  IN  VARCHAR2 DEFAULT ','
) RETURN PARRAY 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;
/

CREATE OR REPLACE PROCEDURE PARSE_CSV_TEST(SearchString IN CLOB, p_cursor OUT   SYS_REFCURSOR) as 
BEGIN

OPEN p_cursor FOR
select * from  TABLE parse_csv(SearchString,','));


END;
/

DECLARE
    -- Variable declarations
    l_SEARCHSTRING   CLOB;
    l_T_CURSOR       SYS_REFCURSOR;
BEGIN
    -- Variable initializations
    l_SEARCHSTRING :=
        TO_CLOB (
            'PAR000015,PAR000016,PAR000017,PAR000018,PAR000019,PAR000020,PAR000021,PAR000022,PAR000023,PAR000024,
PAR000025,PAR000026,PAR000027,PAR000028,PAR000029,PAR000030,PAR000031,PAR000032,PAR000033,PAR000034,
PAR000035,PAR000036,PAR000037,PAR000038,PAR000039,PAR000040,PAR000041,PAR000042,PAR000043,PAR000044,
PAR000045,PAR000046,PAR000047,PAR000048,PAR000049,PAR000050,PAR000051,PAR000052,PAR000053,PAR000054,
PAR000055,PAR000056,PAR000057,PAR000058,PAR000059,PAR000060,PAR000061,PAR000062,PAR000063,PAR000064,
PAR000065,PAR000066,PAR000067,PAR000068,PAR000069,PAR000070,PAR000071');

    -- Call
    PARSE_CSV_TEST (SEARCHSTRING   => l_SEARCHSTRING,
                            T_CURSOR       => l_T_CURSOR);

    -- Transaction control
    COMMIT;

    -- Output bind variables, do not modify
     :2 := l_T_CURSOR;
END;

CREATE OR REPLACE TYPE PARRAY as table of CLOB;
/
SHOW ERRORS
CREATE OR REPLACE FUNCTION parse_csv(
  i_str    IN  CLOB,
  i_delim  IN  VARCHAR2 DEFAULT ','
) RETURN PARRAY 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;
/
SHOW ERRORS
CREATE OR REPLACE PROCEDURE PARSE_CSV_TEST(SearchString IN CLOB, p_cursor OUT   SYS_REFCURSOR) as 
BEGIN

-- added a left parenthesis between table and parse:
OPEN p_cursor FOR
select * from  TABLE (parse_csv(SearchString,','));


END;
/
SHOW ERRORS
-- added variable:
VARIABLE g_refcur  REFCURSOR
DECLARE
    -- Variable declarations
    l_SEARCHSTRING   CLOB;
    l_T_CURSOR       SYS_REFCURSOR;
BEGIN
    -- Variable initializations
    l_SEARCHSTRING :=
        TO_CLOB (
            'PAR000015,PAR000016,PAR000017,PAR000018,PAR000019,PAR000020,PAR000021,PAR000022,PAR000023,PAR000024,PAR000025,PAR000026,PAR000027,PAR000028,PAR000029,PAR000030,PAR000031,PAR000032,PAR000033,PAR000034,PAR000035,PAR000036,PAR000037,PAR000038,PAR000039,PAR000040,PAR000041,PAR000042,PAR000043,PAR000044,PAR000045,PAR000046,PAR000047,PAR000048,PAR000049,PAR000050,PAR000051,PAR000052,PAR000053,PAR000054,PAR000055,PAR000056,PAR000057,PAR000058,PAR000059,PAR000060,PAR000061,PAR000062,PAR000063,PAR000064,PAR000065,PAR000066,PAR000067,PAR000068,PAR000069,PAR000070,PAR000071');
-- replaced line above wit lines below
    l_SEARCHSTRING := EMPTY_CLOB();
  FOR i IN 1 .. 4000 LOOP
    l_SEARCHSTRING := l_SEARCHSTRING || 'PAR' || LPAD (i + 14, 6, '0') || ',';
  END LOOP;



    -- Call
-- changed t_cursor below to p_cursor, it must match the name in the parse_csv_test procedure:
    PARSE_CSV_TEST (SEARCHSTRING   => l_SEARCHSTRING,
                            P_CURSOR       => l_T_CURSOR);

    -- Transaction control
    COMMIT;
 
/* disabled this section
    -- Output bind variables, do not modify
     :2 := l_T_CURSOR;
*/
-- added line below
  :g_refcur := l_T_CURSOR;
END;
/


-- added line to print results
PRINT g_refcur

-- replaced line above wit lines below
    l_SEARCHSTRING := EMPTY_CLOB();
  FOR i IN 1 .. 4000 LOOP
    l_SEARCHSTRING := l_SEARCHSTRING || 'PAR' || LPAD (i + 14, 6, '0') || ',';
  END LOOP;