텍스트를 구분자로 여러 ROW로 바꾸기

CREATE OR REPLACE FUNCTION FAC.FN_SPLITROW

(
    I_VALUE VARCHAR2,
    I_DELIMETER VARCHAR2 := ','
)
RETURN VARCHAR2_ARRAY_4000
PIPELINED
IS
    L_IDX    PLS_INTEGER;
    L_REST_VALUE    VARCHAR2(32767) := I_VALUE;
BEGIN
    LOOP
        L_IDX := INSTR(L_REST_VALUE, I_DELIMETER);
        IF L_IDX > 0 THEN
            PIPE ROW(TRIM(SUBSTR(L_REST_VALUE, 1, L_IDX - 1)));
            L_REST_VALUE := SUBSTR(L_REST_VALUE, L_IDX + LENGTH(I_DELIMETER));
        ELSE
            PIPE ROW(TRIM(L_REST_VALUE));
            EXIT;
        END IF;
    END LOOP;
    RETURN;
END FN_SPLITROW;
/

사용방법

SELECT COLUMN_VALUE
FROM TABLE(FAC.FN_SPLITROW('1,2,3',','))