open c_List for
  select * from table(cast(split(p_String, p_del) as split_tbl));

SQL> CREATE OR REPLACE TYPE split_tbl AS TABLE OF VARCHAR2(4000)
  2  /

Type created.

SQL> CREATE OR REPLACE
  2  FUNCTION split
  3  (
  4  p_list varchar2,
  5  p_del varchar2 :=','
  6  ) return split_tbl pipelined
  7  is
  8  l_idx pls_integer;
  9  l_list varchar2(32767);
 10  l_TestString varchar2(32767);
 11  begin
 12  l_TestString := substr(p_list,1,1);
 13  if l_TestString = ',' then
 14  l_list := ltrim(p_list,p_del);
 15  else
 16  l_list := p_list;
 17  end if;
 18  l_TestString := substr(p_list,length(p_list),1);
 19  if l_TestString = ',' then
 20  l_list := rtrim(l_list,p_del);
 21  end if;
 22  loop
 23  l_idx := instr(l_list,p_del);
 24  if l_idx >0 then
 25  pipe row(substr(l_list,1,l_idx-1));
 26  l_list := substr(l_list,l_idx+length(p_del));
 27  else
 28  pipe row(l_list);
 29  exit;
 30  end if;
 31  end loop;
 32  return;
 33  end split;
 34  /

Function created.

SQL> CREATE OR REPLACE PACKAGE test_pack
  2  AS
  3    PROCEDURE x;
  4  END test_pack;
  5  /

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY test_pack
  2  AS
  3    TYPE r_cursor IS REF CURSOR;
  4  
  5    PROCEDURE x
  6    IS
  7      curs   r_cursor;
  8      out_put  VARCHAR2(1);
  9    BEGIN
 10      OPEN curs FOR SELECT * FROM TABLE(split('x,y,z'));
 11  
 12      LOOP
 13        FETCH curs INTO out_put;
 14        EXIT WHEN curs%NOTFOUND;
 15        dbms_output.put_line('OUTPUT : ' || out_put);
 16      END LOOP;
 17    END x;
 18  
 19  END test_pack;
 20  /

Package body created.

SQL> EXEC test_pack.x;
OUTPUT : x
OUTPUT : y
OUTPUT : z

PL/SQL procedure successfully completed.

select * from table(cast(split(p_main_item_id) as split_tbl))

type split_tbl is table of varchar2(32767);
function split(p_List varchar2,p_del varchar2 := ',') return split_tbl pipelined is
    l_idx    pls_integer;
    l_list    varchar2(32767) := p_List;
    l_value    varchar2(32767);
begin
    loop
        l_idx := instr(l_list,p_del);
        if l_idx > 0 then
            pipe row(substr(l_list,1,l_idx-1));
            l_list := substr(l_list,l_idx+length(p_del));

        else
            pipe row(l_list);
            exit;
        end if;
    end loop;
    return;
end split;
procedure build_list(p_List varchar2)
     curr_ap_id product_type;
  BEGIN
      for main in ( select * 
                    from ITEM_LIST i
                    where  i.item_id in (select * from table(cast(split(p_main_item_id) as split_tbl)))                          ) loop

select * from table(cast(split(p_List) as split_tbl))

select * from dual

create or replace type split_tbl is table of varchar2(32767);

create or replace function split1(p_List varchar2,p_del varchar2 := ',') return split_tbl pipelined is
    l_idx    pls_integer;
    l_list    varchar2(32767) := p_List;
    l_value    varchar2(32767);
begin
    loop
        l_idx := instr(l_list,p_del);
        if l_idx > 0 then
            pipe row(substr(l_list,1,l_idx-1));
            l_list := substr(l_list,l_idx+length(p_del));
        else
            pipe row(l_list);
            exit;
        end if;
    end loop;
    return;
end split1;

create or replace procedure printList(p_List varchar2) IS
  BEGIN
      create table test_jk as select * from table(cast(split1(p_List)as split_tbl));
      
 end printList;

Type created.
Function created.
Warning: PROCEDURE created with compilation errors.
PLS-00103: CREATE symbol encountered

SQL> create or replace type split_tbl is table of varchar2(32767);
  2  /

Type created.

SQL> create or replace function split1(p_List varchar2,p_del varchar2 := ',') return split_tbl pipelined is
  2      l_idx    pls_integer;
  3      l_list    varchar2(32767) := p_List;
  4      l_value    varchar2(32767);
  5  begin
  6      loop
  7          l_idx := instr(l_list,p_del);
  8          if l_idx > 0 then
  9              pipe row(substr(l_list,1,l_idx-1));
 10              l_list := substr(l_list,l_idx+length(p_del));
 11          else
 12              pipe row(l_list);
 13              exit;
 14          end if;
 15      end loop;
 16      return;
 17  end split1;
 18  /

Function created.

SQL> create or replace procedure printList(p_List varchar2) IS
  2    BEGIN
  3        create table test_jk as select * from table(cast(split1(p_List)as split_tbl));
  4        
  5   end printList;
  6  /

Warning: Procedure created with compilation errors.

SQL> show errors
Errors for PROCEDURE PRINTLIST:
LINE/COL
---------------------------------------------------------------------------------
ERROR
------------------------------------------------------------------------------------------------------------
3/7
PLS-00103: Encountered the symbol "CREATE" when expecting one of the following:

   begin case declare exit for goto if loop mod null pragma
   raise return select update while with <an identifier>
   <a double-quoted delimited-identifier> <a bind variable> <<
   close current delete fetch lock insert open rollback
   savepoint set sql execute commit forall merge pipe

SQL> var p_list varchar2(30)
SQL> exec :p_list := 'a,b,c';

PL/SQL procedure successfully completed.

SQL> create table t (id varchar2(20));

Table created.

SQL> insert into t select * from table(cast(split1(:p_List)as split_tbl));

3 rows created.

SQL> select * from t;
ID
--------------------
a
b
c

3 rows selected.

SQL> edit
Wrote file afiedt.buf

  1  CREATE OR REPLACE package body items_pkg as
  2   type split_tbl is table of varchar2(32767);
  3   function split1(p_List varchar2,p_del varchar2 := ',') return split_tbl pi
pelined is
  4      l_idx    pls_integer;
  5      l_list    varchar2(32767) := p_List;
  6      l_value    varchar2(32767);
  7  begin
  8      loop
  9          l_idx := instr(l_list,p_del);
 10          if l_idx > 0 then
 11              pipe row(substr(l_list,1,l_idx-1));
 12              l_list := substr(l_list,l_idx+length(p_del));
 13          else
 14              pipe row(l_list);
 15              exit;
 16          end if;
 17      end loop;
 18      return;
 19  end split1;
 20   procedure printList(p_List varchar2) IS
 21    BEGIN
 22        insert into test_jk select * from table(split1(p_List));
 23   end printList;
 24* end items_pkg;
SQL> /

Warning: Package Body created with compilation errors.

SQL> show errors;
Errors for PACKAGE BODY ITEMS_PKG:

LINE/COL ERROR
-------- -----------------------------------------------------------------
22/7     PL/SQL: SQL Statement ignored
22/47    PLS-00231: function 'SPLIT1' may not be used in SQL
22/47    PL/SQL: ORA-00904: : invalid identifier

create or replace package items_pkg as
	type split_tbl is table of varchar2(32767);
	function split1(p_List varchar2,p_del varchar2 := ',')return split_tbl pipelined;
	procedure printList1(p_List varchar2);
end items_pkg;


CREATE OR REPLACE package body items_pkg is


 function split1(p_List varchar2,p_del varchar2 := ',') return split_tbl pipelined is
    l_idx    pls_integer;
    l_list    varchar2(32767) := p_List;
    l_value    varchar2(32767);
begin
    loop
        l_idx := instr(l_list,p_del);
        if l_idx > 0 then
            pipe row(substr(l_list,1,l_idx-1));
            l_list := substr(l_list,l_idx+length(p_del));
        else
            pipe row(l_list);
            exit;
        end if;
    end loop;
    return;
end split1;

 procedure printList1(p_List varchar2) IS
  BEGIN
	
      insert into test_jk select * from table(cast(split1(p_List)as split_tbl));
      
 end printList;
end items_pkg;