listagg长度过长问题

截取

比如有如下的语句:

select LISTAGG (a.nename,',') within group (order by a.nename) nename from   
    (select distinct d.ne_name as nename from t_eom_alarm_define d where d.work_id=#PM_ID#) a

如果异常则可以截取:

select dbms_lob.substr(wm_concat(a.nename),2000,1)nename from (select distinct d.ne_name as nename  
          from t_eom_alarm_define d  
         where d.work_id = #PM_ID#) a

使用XML的方式解决

可以使用如下的方式来解决:

-- "here 'a' is a column in table 'emp'"  
SELECT  
    RTRIM (  
        XMLAGG (XMLELEMENT (e, x.a || ',')).EXTRACT ('//text()'), ','  
      ) empnos  
FROM emp x;

构成XMLElement元素e,其内容为列a的内容。

参考文章:

https://blog.csdn.net/luman1991/article/details/52712472
https://community.oracle.com/thread/3938097
https://stackoverflow.com/questions/11541383/ordering-by-list-of-strings-in-oracle-sql-without-listagg
https://www.experts-exchange.com/questions/26392332/Oracle-WM-Concat-Error-String-Buffer-too-small.html
https://community.oracle.com/message/11243436
https://community.oracle.com/message/10601105#10601105

若文章对您有帮助,请打赏1块钱。您的支持,可以让我分享更多精彩的文章。转载请注明来源


知识共享许可协议
本作品采用知识共享署名-非商业性使用-相同方式共享 2.5 中国大陆许可协议进行许可。