CREATE OR REPLACE FUNCTION DWH_HTMLDB.SPLIT (p_list    CLOB,
                                                                        p_del    VARCHAR2 := ';')
   RETURN split_tbl
   PIPELINED
IS
   l_idx     PLS_INTEGER;
   s_idx     PLS_INTEGER;
   e_idx     PLS_INTEGER :=  1 ;
   del_len   PLS_INTEGER;
   i         PLS_INTEGER :=  0 ;
   l_list    VARCHAR2 ( 32767 ) := p_list;
   l_value   VARCHAR2 ( 100 );
BEGIN
   del_len := LENGTH (p_del);

   LOOP
      i := i +  1 ;
      l_idx :=
         INSTR (l_list,
                p_del,
                 1 ,
                i);

      IF i =  1 
      THEN
         s_idx := e_idx;
      ELSE
         s_idx := e_idx + del_len;
      END IF;

      IF l_idx >  0 
      THEN
         PIPE ROW (SUBSTR (l_list, s_idx, l_idx - s_idx));
      ELSE
         PIPE ROW (SUBSTR (l_list, s_idx));
         EXIT;
      END IF;

      e_idx := l_idx;
   END LOOP;

   RETURN;
END split;
/