PROMPT Type creation
CREATE TYPE vc2_type AS OBJECT ( the_value VARCHAR2(255));
/
CREATE TYPE vc2_tab AS TABLE OF vc2_type;
/
PROMPT Function creation
CREATE OR REPLACE FUNCTION string_to_tab(piv_string IN VARCHAR2, piv_delimiter IN VARCHAR2)
RETURN vc2_tab PIPELINED DETERMINISTIC
IS
  v_string VARCHAR2(4000) DEFAULT piv_string;
  v_offset PLS_INTEGER;
BEGIN
  LOOP
    v_offset := INSTR(v_string, piv_delimiter);
    IF v_offset > 0 THEN
      PIPE ROW ( new vc2_type( SUBSTR(v_string, 1, v_offset-1)
                             )
               );
      v_string := SUBSTR(v_string, v_offset+1, LENGTH(v_string));
    END IF;
    EXIT WHEN v_string Is NULL OR v_offset = 0;
  END LOOP;
  IF v_string is not null then
    PIPE ROW ( new vc2_type( v_string ) );
  END IF;
  RETURN;
END string_to_tab;
/