Oracle查询中如何将VARRAY类型值转换为拼接文本列表

完全可以仅通过原生SQL语句实现VARRAY类型字段到拼接文本列表的转换，无需额外创建自定义函数或存储过程，适配所有不支持ADT类型返回的查询客户端。

Oracle 18c环境中，部分查询驱动不支持抽象数据类型（ADT，含VARRAY、SDO_GEOMETRY等类型）的直接读取，会抛出ORA-00932: inconsistent datatypes: expected CHAR got ADT错误，部分前端展示层会直接将该错误渲染为空结果集，存在误导性。

以下实现均以测试数据sys.odcivarchar2list('a', 'b', 'c')为例，可直接替换为实际表中的VARRAY字段使用：

• 格式1：带类型定义的完整格式（与SQL Developer默认展示效果一致）
  输出结果：SYS.ODCIVARCHAR2LIST('a', 'b', 'c')

WITH data AS (SELECT sys.odcivarchar2list('a', 'b', 'c') AS my_array FROM dual)
SELECT
  anydata.getTypeName(anydata.convertCollection(my_array))
  || '(' || listagg('''' || column_value || '''', ', ') WITHIN GROUP (ORDER BY rnum) || ')' AS my_array
FROM (
  SELECT my_array, column_value, ROWNUM AS rnum 
  FROM data, TABLE(my_array)
)
GROUP BY my_array

• 格式2：带单引号的逗号分隔元素列表
  输出结果：'a', 'b', 'c'

WITH data AS (SELECT sys.odcivarchar2list('a', 'b', 'c') AS my_array FROM dual)
SELECT
  listagg('''' || column_value || '''', ', ') WITHIN GROUP (ORDER BY rnum) AS my_array
FROM (
  SELECT my_array, column_value, ROWNUM AS rnum 
  FROM data, TABLE(my_array)
)
GROUP BY my_array

• 格式3：无引号纯值逗号分隔列表
  输出结果：a,b,c

WITH data AS (SELECT sys.odcivarchar2list('a', 'b', 'c') AS my_array FROM dual)
SELECT
  listagg(column_value, ',') WITHIN GROUP (ORDER BY rnum) AS my_array
FROM (
  SELECT my_array, column_value, ROWNUM AS rnum 
  FROM data, TABLE(my_array)
)
GROUP BY my_array

• 核心逻辑为通过TABLE()函数将VARRAY数组展开为多行数据，再通过listagg()聚合函数完成字符串拼接
• 子查询中生成的rnum字段用于保证元素拼接顺序与VARRAY原始定义顺序完全一致
• 若VARRAY元素值本身包含单引号，可将拼接逻辑中的column_value替换为REPLACE(column_value, '''', '''''')做转义，避免输出格式异常
• 该方案兼容Oracle 11gR2及以上所有版本，需保证数据库字符集支持拼接后的字符串长度。

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