짜증내지 말자... 신경질 내지 말자... 고운 말을 쓰자...

출처 : http://blog.naver.com/addibuddi?Redirect=Log&logNo=12259012

-- TYPE 을 생성
create or replace type split_tbl as table of varchar2(32767);

/
show errors;
/*
테이블 형식으로 조회 됩니다.
*/
CREATE OR REPLACE FUNCTION split
(
    p_list varchar2,
    p_del varchar2 := ','
)
RETURN split_tbl PIPELINED
IS
    l_idx    pls_integer;
    l_list   VARCHAR2(32767) := p_list;
    l_value  VARCHAR2(32767);
BEGIN
    LOOP
        l_idx := INSTR(l_list, p_del);
        IF l_idx > 0 then
            PIPE ROW(SUBSTR(l_list, 1, l_idx-1));
            l_list := SUBSTR(l_list, l_idx + LENGTH(p_del));       
        ELSE
            IF LENGTH(l_list) > 0 THEN
                PIPE ROW(l_list);
            END IF;
            EXIT;
        END IF;
    END LOOP;
    RETURN;
END split;
/
show errors;

With this function, I can run a query like this:

SQL> select * from table(split('one,two,three,')); 또는 select * from table(split('one,two,three'));

one
two
three