SQL SYS.ODCIVARCHAR2LIST이용 unionall 대체

SYS.ODCIVARCHAR2LIST이용 union all 대체

• CTE 구문

SELECT '1' code, 'one' code_name FROM dual UNION ALL
SELECT '2' code, 'two' code_name FROM dual UNION ALL  
SELECT '3' code, 'three' code_name FROM dual;

• regexp_substr함수 와 SYS.ODCIVARCHAR2LIST함수 이용 union all 대체

SELECT regexp_substr(column_value,'[^|]+',1,1) code -- '|'구분자 기준 1번째값
     , regexp_substr(column_value,'[^|]+',1,2) code_name -- '|'구분자 기준 2번째값  
  FROM TABLE(SYS.ODCIVARCHAR2LIST('1|one', '2|two', '3|three'));