2021-06-02 Solve the problem that the listagg concatenation field returns too long (Ora-01489 result of String concatenation is too long)

When querying data in Oracle, you sometimes need to use column to row to splice multiple rows together. Generally, I am used to using the listagg() function, but if there are too many spliced ​​records, an error will be reported. The
return type of listagg splicing is varchar, the maximum length Is 4000, when the length is too long, an error will be reported,ora-01489 result of String concatenation is too long

The solution at this time is to convert the returned varchar type to the clob type, so that the length can meet the requirements of the return value.

1.Using the xmlagg function, the usage is similar to that of listagg, but there are some differences

Suppose there is a table user(id, name, dept) and now you want to find out the user id and name of each department.
-- 语法
xmlagg(xmlparse(content 拼接字段 || ','wellformed) order by 排序字段).getclobval()

select dept, xmlagg(xmlparse(content id || ',' wellformed) order by id).getclobval() ids,xmlagg(xmlparse(content name|| ',' wellformed) order by id).getclobval() names
from user group by dept
The xmlparse function can be understood as organizing the data inside into xml documents, and then using the xmlagg method to splice these xml instances. It should be noted that xmlparse 中content 后面跟的是每行数据需要拼接的部分each line of data is connected with a ,comma, so there will be one at the end of the last line Comma, so you need to remove the last comma. Listagg is a splicing symbol in the connection part between multiple elements, and xmlagg just splices multiple xml instances, so there will be one more ,.

Improved version

select dept, 
rtrim(xmlagg(xmlparse(content id || ',' wellformed) order by id).getclobval(),',' ) ids,  
rtrim( xmlagg(xmlparse(content name|| ',' wellformed) order by id).getclobval(),',') names
from user group by dept

I saw that there are also instances of using xmlelement to return XMLType on the Internet, so I recorded it together.This function feels similar to the xmlparse function. It also splices the value of each xml instance. Because each line of the setting is spliced ​​at the end ,, rtrim is also needed to remove it. The last symbol can also be found in java and then processed.

 xmlagg( xmlelement(e,拼接字段,',').extract('//text()') order by 排序字段 ).getclobval()

-- 不去逗号版
select dept,
xmlagg(xmlelement(e,id,',').extract('//tetx()') order by id).getclobval() ids,
xmlagg(xmlelement(e,name,',').extract('//text()') order by id).getclobval() names
from user group by dept

-- 去掉末尾的连接符号版
select dept,
rtrim( xmlagg(xmlelement(e,id,',').extract('//tetx()') order by id).getclobval(),',') ids,
rtrim(xmlagg(xmlelement(e,name,',').extract('//text()') order by id).getclobval(),',') names
from user group by dept

Introduction to oracle xml processing function

  1. EXTRACT (XMLType_instance, Xpath_string)
    This function is used to return the corresponding content under the path of the XML node.
SELECT extract(value(a),'/root/main') data FROM xmltable a ;
  1. SYS_XMLAGG (expr[, fmt])
    This function is used to summarize all XML documents and generate an XML document.


SELECT SYS_XMLAGG(SYS_XMLGEN(a.order_content)) xml_content
from doc_clinic_order a, clinics_item b, med_frequencydict c
where a.patient_id = 'bd4b425e-a409-4b28-890d-d1d668fcf725'
and a.parentid = '0'
and a.route = b.item_id(+)
and a.frequency = c.frequency_id(+)
  1. XMLAGG (XMLType_instance[ORDER BY sort_list])
    This function is used to aggregate multiple XML blocks and generate XML documents.
select xmlagg(xmlelement("row",xmlforest(a.frequency_id as "频次ID", a.frequency_name as "频次名称")))[]
from med_frequencydict a
  1. XMLELEMENT(identifier[,xml_attribute_clause][,value_expr])
    This function is used to return an instance of XMLType. The parameter identifier is used to specify the element name, the parameter xml_attribute_clause is used to specify the element attribute clause, and the parameter value_expr is used to specify the element value.
SELECT xmlelement("row",xmlcolattval(a.frequency_code AS "编码",a.frequency_name as "名称")) xml
from med_frequencydict a
where a.frequency_code = 'BID'
xml processing functions refer to https://www.jb51.net/article/84876.htm Script House, if you want to know more xml processing functions, you can go to this website for details