WITH TEST1 AS (SELECT LISTAGG(COLUMN_NAME, ',') WITHIN GROUP(ORDER BY COLUMN_ID) WORKING FROM USER_TAB_COLUMNS WHERE TABLE_NAME = 'EMP'),
TEST2 AS (SELECT REGEXP_SUBSTR(WORKING, '[^,]+', 1, LEVEL, 'i') STR FROM TEST1 CONNECT BY
LEVEL <= LENGTH(WORKING) - LENGTH(REGEXP_REPLACE(WORKING,',','')) + 1)
SELECT * FROM TEST2
STR
--------------------------------------------------------------------------------
EMPNO
ENAME
HIREDATE
SAL
DEPTNO
