oracle sql将数据拼接导出（ORA-01489: result of string concatenation is too long）

关键字：plsql、oracle、listagg、xmlagg、<CLOB>、ORA-01489: result of string concatenation is too long

1. 在使用listagg将数据拼接查询时报错，以下是原本的sql

 select tu.unit_name ,
        th.heating_name  ,
        tu.legal_person_landline  ,
        listagg(td.road||td.cell_name||td.building_name, ',') within group(order by td.heating_source_id) 
   from t_heating_souce_base th
  inner join t_heating_unit_baseinfo tu
     on th.unit_id = tu.id
  inner join T_R_BUILDING_DETAILS td
     on td.heating_source_id = th.id
  inner join t_administrative_code tc
     on tc.code = td.county
  where th.is_delete = '0'
    and td.is_delete = '0'
  group by tu.unit_name ,
        th.heating_name  ,
        tu.legal_person_landline
 

2. 说字段过长ORA-01489: result of string concatenation is too long

3. 所以使用xmlagg查询数据

 select tu.unit_name ,
        th.heating_name  ,
        tu.legal_person_landline  ,
        xmlagg(xmlparse(content td.road||td.cell_name||td.building_name || ',' wellformed) ORDER BY td.heating_source_id).getclobval()
   from t_heating_souce_base th
  inner join t_heating_unit_baseinfo tu
     on th.unit_id = tu.id
  inner join T_R_BUILDING_DETAILS td
     on td.heating_source_id = th.id
  inner join t_administrative_code tc
     on tc.code = td.county
  where th.is_delete = '0'
    and td.is_delete = '0'
  group by tu.unit_name ,
        th.heating_name  ,
        tu.legal_person_landline

4. plsql工具查询到的数据是<CLOB>,这时候我们不管是复制数据，还是右键点击复制后导出指定文件都不行，他会将<CLOB>原样导出，这时候我们需要点击导出查询结果按钮进行导出即可。