REGEXP_SUBSTR을 활용해서 만들었습니다.
SELECT ROWNUM, REGEXP_SUBSTR(T.V, '[^^]+', 1, 1) YR, REGEXP_SUBSTR(T.V, '[^^]+', 1, 2) SEQ, REGEXP_SUBSTR(T.V, '[^^]+', 1, 3) NM FROM ( SELECT REGEXP_SUBSTR ( R, '[^|]+', 1, LEVEL ) V FROM (SELECT REPLACE(REMARK,CHR(10),'|') R FROM TEST) CONNECT BY LEVEL <= LENGTH ( R ) - LENGTH ( REPLACE ( R, '|' ) ) + 1 ) T