oracle 使用listagg函数报 ORA-01489: result of string concatenation is too long错误处理

超、凢脫俗 2023-02-24 03:49 9阅读 0赞

由于oracle 19c不能使用wm\_concat函数，只能使用listagg进行列转行。 在使用时遇到如下错误  
ORA-01489: result of string concatenation is too long

SELECT t.tablespace_name,
                       listagg(t.table_name, ',') WITHIN GROUP(ORDER BY table_name) over(PARTITION BY tablespace_name) clause
                  FROM user_tables t;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3doYW5kZ2Ro_size_16_color_FFFFFF_t_70]  
错误是由于oracle对字符串长度有限制，长度不能超过4000.超过4000以后需要转为clob类型。  
解决办法使用oracle的另外一个函数xmlagg。

SELECT t.tablespace_name,
                           xmlagg(xmlparse(content t.table_name || ',' wellformed) ORDER BY t.table_name).getclobval()
                      FROM user_tables t
                     GROUP BY t.tablespace_name;

查询结果  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3doYW5kZ2Ro_size_16_color_FFFFFF_t_70 1]  
看到合并后的内容后面多了一个连接符。所以还可以使用**rtrim**函数去掉多余的连接符

SELECT t.tablespace_name,
                           rtrim(xmlagg(xmlparse(content t.table_name || ',' wellformed) ORDER BY t.table_name).getclobval(),
                                 ',')
                      FROM user_tables t
                     GROUP BY t.tablespace_name;

增加**rtrim**函数后，连接内容不再有多余的连接符  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3doYW5kZ2Ro_size_16_color_FFFFFF_t_70 2]

但是使用xmlagg函数将内容转为clob后也会有个缺点，就是clob不能使用聚合函数，也不能进行group by 或者去重后。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3doYW5kZ2Ro_size_16_color_FFFFFF_t_70]: /images/20230209/394118c69719452b840259c3b7cb9912.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3doYW5kZ2Ro_size_16_color_FFFFFF_t_70 1]: /images/20230209/b658130ba1794b9189dc6b10ed64c35c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3doYW5kZ2Ro_size_16_color_FFFFFF_t_70 2]: /images/20230209/e93ae4c8ef184d9ea769955845abd485.png