参考编号-oracle-developer.net
下面是我的Oracle查询-

-- User-Defined TYPE
create or replace TYPE varchar2_ntt AS TABLE OF VARCHAR2(4000);

-- User-Defined FUNCTION
CREATE FUNCTION to_string (
                    nt_in        IN varchar2_ntt,
                    delimiter_in IN VARCHAR2 DEFAULT ','
                    ) RETURN VARCHAR2 IS
  
       v_idx PLS_INTEGER;
       v_str VARCHAR2(32767);
       v_dlm VARCHAR2(10);
  
   BEGIN
 
      v_idx := nt_in.FIRST;
      WHILE v_idx IS NOT NULL LOOP
         v_str := v_str || v_dlm || nt_in(v_idx);
         v_dlm := delimiter_in;
         v_idx := nt_in.NEXT(v_idx);
      END LOOP;
 
      RETURN v_str;
 
   END to_string;

-- Simple Example
SELECT deptno,
        TO_STRING(CAST(COLLECT(ename) AS varchar2_ntt)) AS emps
FROM   emp
    GROUP BY  deptno;

字符串
我尝试在多个列上实现Collect聚合函数。它工作正常，没有我的用户定义函数TO_STRING。

-- This works
select emp.DEPT_NAME,
        CAST(
            COLLECT(emp.EMP_ID || ':' || emp.EMP_JOIN_DATE) 
        AS varchar2_ntt) AS EMPS
from  employee emp 
    group by  emp.DEPT_NAME;
    
-- Output
DEPT_NAME   EMPS

SALES       TEST_DB.VARCHAR2_NTT('750127:20-JAN-23', '750228:20-JAN-23')
FINANCE     TEST_DB.VARCHAR2_NTT('548834:10-JAN-19', '802850:14-MAR-23', '802849:19-OCT-23')

型
但是当我尝试在cast(collect(...))上实现TO_STRING时，它失败了。

--This doesn't work 
select emp.DEPT_NAME,
        TO_STRING(CAST(
            COLLECT(emp.EMP_ID || ':' || emp.EMP_JOIN_DATE) 
        AS varchar2_ntt)) AS EMPS
from  employee emp 
    group by  emp.DEPT_NAME;

型
错误代码：

ORA-06502: PL/SQL: numeric or value error: character string buffer too small
06502. 00000 -  "PL/SQL: numeric or value error%s"
*Cause:    An arithmetic, numeric, string, conversion, or constraint error
           occurred. For example, this error occurs if an attempt is made to
           assign the value NULL to a variable declared NOT NULL, or if an
           attempt is made to assign an integer larger than 99 to a variable
           declared NUMBER(2).
*Action:   Change the data, how it is manipulated, or how it is declared so
           that values do not violate constraints.

型
如何解决这个错误？
我可以使用LISTAGG和XMLAGG实现上述功能。但就性能而言，COLLECT比这2个更有效。LISTAGG有大小限制，而XMLAGG非常慢。