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


Posted by Vanchy on 2021-03-03

* 發生原因

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<

* Reference

https://stackoverflow.com/questions/13795220/listagg-function-result-of-string-concatenation-is-too-long

https://stackoverflow.com/questions/11744465/xpath-difference-between-node-and-text


#oracle







Related Posts

簡明 Python LINE Bot & LIFF JS SDK  入門教學

簡明 Python LINE Bot & LIFF JS SDK 入門教學

《鳥哥 Linux 私房菜:基礎篇》Chapter 05 - Linux 的檔案權限與目錄配置

《鳥哥 Linux 私房菜:基礎篇》Chapter 05 - Linux 的檔案權限與目錄配置

位元運算

位元運算


Comments