Разбиение строк на лексемы

CREATE OR REPLACE
  PACKAGE CONVERT_PKG
    IS
      TYPE STRING_TBL_TYPE
        IS
          TABLE OF VARCHAR2(4000);
      FUNCTION LIST_TO_TABLE(
                             p_LIST                 IN VARCHAR2,
                             p_DELIMITER            IN VARCHAR2 DEFAULT ','
                            )
        RETURN STRING_TBL_TYPE;
      FUNCTION TABLE_TO_LIST(
                             p_TBL                  IN STRING_TBL_TYPE,
                             p_DELIMITER            IN VARCHAR2 DEFAULT ','
                            )
        RETURN VARCHAR2;
END;
/

CREATE OR REPLACE
  PACKAGE BODY CONVERT_PKG
    IS
      FUNCTION LIST_TO_TABLE(
                             p_LIST                 IN VARCHAR2,
                             p_DELIMITER            IN VARCHAR2 DEFAULT ','
                            )
        RETURN STRING_TBL_TYPE
        IS
            v_STRING_TBL       STRING_TBL_TYPE := STRING_TBL_TYPE();
            v_START_POSITION   NUMBER;
            v_END_POSTION      NUMBER;
            v_DELIMITER_LENGTH NUMBER;
            v_EXPANDED_LIST    VARCHAR2(32767);
        BEGIN
            IF p_DELIMITER IS NULL
              THEN
                RAISE_APPLICATION_ERROR(
                                        -20500,
                                        'Invalid Delimiter'
                                       );
            END IF;
            v_EXPANDED_LIST    := p_LIST || p_DELIMITER;
            v_DELIMITER_LENGTH := LENGTH(
                                         p_DELIMITER
                                        );
            v_END_POSTION      := 1 - v_DELIMITER_LENGTH;
            LOOP
              v_START_POSITION := v_END_POSTION + v_DELIMITER_LENGTH;
              v_END_POSTION    := INSTR(
                                        v_EXPANDED_LIST,
                                        p_DELIMITER,
                                        v_START_POSITION
                                       );
              EXIT WHEN v_END_POSTION = 0;
              v_STRING_TBL.EXTEND;
              v_STRING_TBL(v_STRING_TBL.LAST) := SUBSTR(
                                                        v_EXPANDED_LIST,
                                                        v_START_POSITION,
                                                        v_END_POSTION - v_START_POSITION
                                                       );
            END LOOP;
            RETURN v_STRING_TBL;
      END;
      FUNCTION TABLE_TO_LIST(
                             p_TBL                  IN STRING_TBL_TYPE,
                             p_DELIMITER            IN VARCHAR2 DEFAULT ','
                            )
        RETURN VARCHAR2
        IS
            v_LIST   VARCHAR2(32767);
        BEGIN
            IF p_DELIMITER IS NULL
              THEN
                RAISE_APPLICATION_ERROR(
                                        -20500,
                                        'Invalid Delimiter'
                                       );
            END IF;
            FOR v_I IN 1..p_TBL.LAST LOOP
              IF INSTR(
                       p_TBL(v_I),
                       p_DELIMITER
                      ) != 0
                THEN
                  RAISE_APPLICATION_ERROR(
                                          -20500,
                                          'Invalid Delimiter'
                                         );
              END IF;
              IF v_I > 1
                THEN
                  v_LIST := v_LIST || p_DELIMITER || p_TBL(v_I);
                ELSE
                  v_LIST := v_LIST || p_TBL(v_I);
              END IF;
            END LOOP;
            RETURN v_LIST;
      END;
END;
/

Или





SELECT SUBSTR(txt,instr(sp
||txt
||sp,sp,1,level),instr(sp
||txt
||sp,sp,2,level) - instr(sp
||txt
||sp,sp,1,level) - 1) pol,
rownum nn
FROM
(SELECT 'qq,ww,ee' AS txt, ',' AS sp FROM dual
)
CONNECT BY level <= LENGTH(regexp_replace(sp
||txt,'[^'
||sp
||']',''))