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.