如何用XMLElement Cast将Oracle APEX查询的邮箱转为横向逗号分隔（规避LISTAGG限制）

在Oracle APEX 20.x环境中，通过POP LOV从前端传入多个ID值后，使用apex_split拆分参数的语句如下：

select column_value as val from table(apex_split(:MYIDS));

实际执行时的语句示例：

select column_value as val from table('3456,89000,8976,5678');

通过以下主查询可返回对应ID的多行邮箱数据：

SELECT email FROM student_details WHERE studid IN (SELECT column_value AS val FROM TABLE(apex_split(:MYIDS)));

返回的多行邮箱示例：

xyz@gmail.com
vbh@gmail.com
ghj@gmail.com
uj1@gmail.com
xy2@gmail.com
vb4@gmail.com
g2j@gmail.com
u8i@gmail.com
x3z@gmail.com
v9h@gmail.com
g00j@gmail.com
uj01@gmail.com

由于LISTAGG存在4000字符长度限制，无法满足大量邮箱拼接需求，可使用XMLElement+XMLAgg+CAST的方法实现无长度限制的单行逗号分隔转换，具体SQL语句如下：

方案1：自动去除末尾逗号 #

SELECT RTRIM(
         XMLAGG(XMLElement(e, email || ',')).EXTRACT('//text()'),
         ','
       ) AS comma_separated_emails
FROM student_details 
WHERE studid IN (SELECT column_value AS val FROM TABLE(apex_split(:MYIDS)));

方案2：转为CLOB类型支持超大长度 #

如果需要支持超过4000字符的拼接，直接转为CLOB类型：

SELECT CAST(
         XMLAGG(XMLElement(e, email, ',').EXTRACT('//text()') ORDER BY email) 
         AS CLOB
       ) AS comma_separated_emails
FROM student_details 
WHERE studid IN (SELECT column_value AS val FROM TABLE(apex_split(:MYIDS)));

语句说明 #

• XMLElement(e, email, ',')：将每个邮箱与逗号封装为XML元素
• XMLAGG：聚合所有生成的XML元素
• EXTRACT('//text()')：提取XML中的纯文本内容
• CAST(...) AS CLOB：将聚合结果转为CLOB类型，突破字符长度限制
• RTRIM(..., ',')：去除结果末尾多余的逗号

内容的提问来源于stack exchange，提问作者yyy62103