Oracle 11g DB에서 12c, 19c 로 업그레이드 할때 wm_concat 함수 listagg 로 변경 필요

오라클 데이터베이스 10g, 11g 버전을 사용하다가 Oracle 12c 또는 18c, 19c 등으로 업그레이드를 하는 경우 주의해야 할 부분 중의 하나가 wm_concat 함수 입니다.

 

wm_concat 함수는 아래와 같은 데이터가 있을때, 

 

이것을 deptno 를 기준으로 같은 것을 묶어서 옆으로 쭉 나열하고 싶을때 사용합니다.

 

근데, 이 쌈빡한 함수 wm_concat( ) 이 사실은 오라클에서 공식으로 발표한 Documented 함수가 아닙니다.

내부 Internal 함수인데, 어떻게 알려지면서 많이 사용하게 되었다고 하네요~  실제로 Oracle 공식매뉴얼에는 없습니다.

 

Oracle 10g 에서 11g 로 올라갈때도 이와 비슷한 경우가 있었습니다.

"bypass_ujvc" 라는 힌트였는데, 이것도 비공식 힌트였죠~

 

어쨌든, 12c 부터는 더이상 wm_concat 함수를 사용할 수 없습니다.

사용하면 "ORA-00904: "WM_CONCAT": invalid identifier" 에러가 발생합니다.

 

대신, listagg 함수를 사용하면 됩니다.  listagg 함수는 Oracle 11g 부터 지원됩니다.

 

select deptno, wm_concat(ename)
from EMP
group by deptno;
=>
select deptno, listagg(ename, ',') within group (order by ename)
from EMP
group by deptno;

 

wm_concat() 함수안에서 아래와 같이 distinct 까지 사용한 경우는 from 절에 inline view 형태로 사용해야 합니다.

 

select deptno, wm_concat(distinct job)
from EMP
group by deptno;
=>
select deptno, listagg(job, ',') within group (order by job)
from (select distinct deptno, job from EMP)
group by deptno;

 

음~ wm_concat 함수가 쓰긴 훨씬 편했는데~ 많이 번거롭네요~~