CREATE OR REPLACE PACKAGE gen_util IS
  -- declaration used for Csv_To_Array
  type Array_t is table of varchar2(200) ;
  --
  /* Example use
    declare
      l_array gen_util.array_t ;
      li_count binary_integer ;
    begin
      gen_util.csv_to_array('"ABC,DEF",123,345,"DEF,GHI",456,', li_count, l_array, ',') ;
      for a in 1..l_array.count loop
        dbms_output.put_line(to_char(a) || ' ' || l_array(a)) ;
      end loop ;
    end ;
    -- gives the output
      ABC,DEF
      123
      345
      DEF,GHI
      456
  */
  procedure Csv_To_Array(
    pv_Csv_String        in  varchar2,
    pi_Count             out binary_integer,
    pa_Array             out array_t,
    pv_Separator         in  varchar2        := ','
  ) ;

END gen_util;
/

CREATE OR REPLACE PACKAGE BODY gen_util IS
  procedure Csv_To_Array(
    pv_Csv_String        in  varchar2,
    pi_Count             out binary_integer,
    pa_Array             out array_t,
    pv_Separator         in  varchar2        := ','
  ) is
    li_start_separator pls_integer     := 0 ;
    li_stop_separator  pls_integer     := 0 ;
    li_length          pls_integer     := 0 ;
    li_idx             binary_integer  := 0 ;
    lb_quote_enclosed  boolean         := false ;
    li_offset          pls_integer     := 1 ;
  begin
    pa_array := array_t() ;
    li_length          := length(pv_Csv_String) ;
    if li_length > 0 then
      loop
        li_Idx := li_Idx + 1 ;
        --
        lb_quote_enclosed := false ;
        if substr(pv_Csv_String, li_start_separator + 1, 1) = '"' then
          lb_quote_enclosed := true ;
          li_offset := 2 ;
          li_stop_Separator := instr(pv_Csv_String, '"', li_start_Separator + li_offset, 1) ;
        else
          li_offset := 1 ;
          li_stop_Separator := instr(pv_Csv_String, pv_Separator, li_start_Separator + li_offset, 1) ;
        end if ;
        if li_stop_Separator = 0 then
          li_stop_Separator := li_length + 1 ;
        end if ;
        --
        Pa_Array.Extend ;
        Pa_Array(li_idx) := (substr(
          pv_Csv_String, li_start_Separator + li_offset,
          (li_stop_Separator - li_start_Separator - li_offset)
        )) ;
        --
        exit when li_stop_Separator >= li_length ;
        if lb_quote_enclosed then
          li_stop_separator := li_stop_separator + 1 ;
        end if ;
        li_start_Separator := li_stop_Separator ;
      end loop ;
    end if ;
    pi_count := li_idx ;
  end Csv_To_Array ;
END gen_util;
/