Oracle Collect function on Multiple Columns gives Error

Question

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.

Answer 1

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