Oracle Collect function on Multiple Columns gives Error

Reference Tutorial- oracle-developer.net

Below is my working Oracle query-

-- 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;

I'm trying to implement Collect aggregate function on multiple columns. It works fine, without my user-defined function 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')

But when I try to implement TO_STRING on cast(collect(...)), it fails.

--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;

Error:

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.

How do I solve this Error?

I'm able to achieve the above using LISTAGG & XMLAGG. But in terms of performance, COLLECT is more efficient than these 2. LISTAGG has size limitations while XMLAGG is very slow.

In the SQL scope, VARCHAR2 is limited to 4000 bytes.

In the PL/SQL scope, VARCHAR2 can be up to 32767 bytes.

If you try to return a string that is too long from a PL/SQL scope to an SQL scope then you will get an ORA-06502: PL/SQL: numeric or value error: character string buffer too small exception.

If your strings are too long then do not return VARCHAR2 and return a CLOB.

CREATE OR REPLACE FUNCTION to_string (
  nt_in        IN varchar2_ntt,
  delimiter_in IN VARCHAR2 DEFAULT ','
) RETURN CLOB
IS
  v_idx PLS_INTEGER;
  v_str CLOB;
BEGIN
  IF nt_in IS NULL THEN
    RETURN NULL;
  END IF;
  v_str := EMPTY_CLOB();
  IF nt_in IS EMPTY THEN
    RETURN v_str;
  END IF;
  v_idx := nt_in.FIRST;
  v_str := nt_in(v_idx);
  LOOP
    v_idx := nt_in.NEXT(v_idx);
    EXIT WHEN v_idx IS NULL;
    v_str := v_str || delimiter_in || nt_in(v_idx);
  END LOOP;
  RETURN v_str;
END to_string;
/

fiddle