[Oracle SQL Debug] 解決ORA-01489的問題

* 發生原因

listagg在官方文件的應用是

As a group-set aggregate, the function operates on and returns an output row for each group defined by the GROUP BY clause.

目的在於使用某個GROUP BY條件，將某欄位的值串接起來
但是這種聚合函數(Aggregate function)最多只能支援4000 Bytes(Varchar2)
若超過4000 Bytes，就會跳出ORA-01489

ORA-01489 result of string concatenation is too long

* 解決方式

1. 升級到Oracle 12

根據Stackflow上的說法，升級到Oracle 12可以設定函數return的長度最高到3200 byte

2. 使用XMLAGG

這篇主要想提到的方法，語法如下：

SELECT dbms_xmlgen.convert(RTRIM(extract(XMLAGG(XMLELEMENT(e, colA, '<') ORDER BY colA, colB), '//text()').getclobval() , ','), 1)
FROM tableA

目的在於回傳一個CLOB型態的字串

語法說明：

注意
資料顯示的部份想表達的是encode後的編碼，但是直接使用會顯示符號，所以每一個字元用空白隔開

XMLELEMENT
● 功能：將非XML格式的資料轉成XML，若有特殊字元，則會自動encode
● 格式：XMLELEMENT("TAG名稱", 欄位, 連接字元)
● 範例：XMLELEMENT("SUBCON", subcon_code, '<')
● 資料：<SUBCON>A & l t ;</SUBCON>

XMLAGG
● 功能：將XML格式的資料聚合成XML格式的文件
● 格式：XMLAGG(XML格式的資料 [ORDER BY語法])
● 範例：XMLAGG(XMLELEMENT("SUBCON", subcon_code, '<') ORDER BY subcon_code)
● 資料：<SUBCON>B & l t ;</SUBCON><SUBCON>C & l t ;</SUBCON><SUBCON>A & l t ;</SUBCON>

EXTRACT
● 功能：從XML格式的資料中擷取想要的部份
● 格式：EXTRACT(XML格式的資料, XPath [, Namespace_str])
● 範例：EXTRACT(XMLAGG(XMLELEMENT("SUBCON", subcon_code, '<') ORDER BY subcon_code), '//text()')
● 資料：B & l t ; C & l t ; A & l t ;
● 備註：'//text()'指的是取出所有第一層Tag下的文字，「//」表示所有

GETCLOBVAL()
● 功能：從XML格式的資料中取得排序後的資料
● 範例：EXTRACT(XMLAGG(XMLELEMENT("SUBCON", subcon_code, '<') ORDER BY subcon_code), '//text()').GETCLOBVAL()
● 資料：A & l t ; B & l t ; C

dbms_xmlgen.convert
● 功能：(※若GETCLOBVAL()產生的字串會有"encode過的字串"，才需要使用這個function)
在dbms_xmlgen Package下的功能都跟轉換成XML格式有關。
convert目的在於轉換成XML格式時，保留特殊字元，不將特殊字元重新編碼過。例如：將「& l t ;」轉換成「<」
● 格式：dbms_xmlgen.convert(XML格式的資料 [, 0或1])
-- 1表示decode
-- 0表示encode
● 範例：dbms_xmlgen.convert(EXTRACT(XMLAGG(XMLELEMENT("SUBCON", subcon_code, '<') ORDER BY subcon_code), '//text()').GETCLOBVAL(), 1)
● 資料：A< B< C<