By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
-- User-Defined TYPE
create or replace TYPE varchar2_ntt AS TABLE OF VARCHAR2(4000);
CREATE TABLE emp (ename VARCHAR2(20), deptno NUMBER(10,0))
INSERT INTO emp (ename, deptno)
SELECT 'Alice', 1 FROM DUAL UNION ALL
SELECT 'Betty', 1 FROM DUAL UNION ALL
SELECT 'Carol', 1 FROM DUAL UNION ALL
SELECT 'Debra', 2 FROM DUAL UNION ALL
SELECT 'Emily', 2 FROM DUAL UNION ALL
SELECT 'Fiona', 3 FROM DUAL UNION ALL
SELECT LPAD(LEVEL, 20, '0'), 4 FROM DUAL CONNECT BY LEVEL <= 200;
206 rows affected
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;
/
SELECT deptno,
TO_STRING(CAST(COLLECT(ename) AS varchar2_ntt)) AS emps
FROM emp
GROUP BY deptno;
| DEPTNO | EMPS |
|---|---|
| 1 | Alice,Carol,Betty |
| 2 | Debra,Emily |
| 3 | Fiona |
| 4 | 00000000000000000001,00000000000000000200,00000000000000000199,00000000000000000198,00000000000000000197,00000000000000000196,00000000000000000195,00000000000000000194,00000000000000000193,00000000000000000192,00000000000000000191,00000000000000000190,00000000000000000189,00000000000000000188,00000000000000000187,00000000000000000186,00000000000000000185,00000000000000000184,00000000000000000183,00000000000000000182,00000000000000000181,00000000000000000180,00000000000000000179,00000000000000000178,00000000000000000177,00000000000000000176,00000000000000000175,00000000000000000174,00000000000000000173,00000000000000000172,00000000000000000171,00000000000000000170,00000000000000000169,00000000000000000168,00000000000000000167,00000000000000000166,00000000000000000165,00000000000000000164,00000000000000000163,00000000000000000162,00000000000000000161,00000000000000000160,00000000000000000159,00000000000000000158,00000000000000000157,00000000000000000156,00000000000000000155,00000000000000000154,00000000000000000153,00000000000000000152,00000000000000000151,00000000000000000150,00000000000000000149,00000000000000000148,00000000000000000147,00000000000000000146,00000000000000000145,00000000000000000144,00000000000000000143,00000000000000000142,00000000000000000141,00000000000000000140,00000000000000000139,00000000000000000138,00000000000000000137,00000000000000000136,00000000000000000135,00000000000000000134,00000000000000000133,00000000000000000132,00000000000000000131,00000000000000000130,00000000000000000129,00000000000000000128,00000000000000000127,00000000000000000126,00000000000000000125,00000000000000000124,00000000000000000123,00000000000000000122,00000000000000000121,00000000000000000120,00000000000000000119,00000000000000000118,00000000000000000117,00000000000000000116,00000000000000000115,00000000000000000114,00000000000000000113,00000000000000000112,00000000000000000111,00000000000000000110,00000000000000000109,00000000000000000108,00000000000000000107,00000000000000000106,00000000000000000105,00000000000000000104,00000000000000000103,00000000000000000102,00000000000000000101,00000000000000000100,00000000000000000099,00000000000000000098,00000000000000000097,00000000000000000096,00000000000000000095,00000000000000000094,00000000000000000093,00000000000000000092,00000000000000000091,00000000000000000090,00000000000000000089,00000000000000000088,00000000000000000087,00000000000000000086,00000000000000000085,00000000000000000084,00000000000000000083,00000000000000000082,00000000000000000081,00000000000000000080,00000000000000000079,00000000000000000078,00000000000000000077,00000000000000000076,00000000000000000075,00000000000000000074,00000000000000000073,00000000000000000072,00000000000000000071,00000000000000000070,00000000000000000069,00000000000000000068,00000000000000000067,00000000000000000066,00000000000000000065,00000000000000000064,00000000000000000063,00000000000000000062,00000000000000000061,00000000000000000060,00000000000000000059,00000000000000000058,00000000000000000057,00000000000000000056,00000000000000000055,00000000000000000054,00000000000000000053,00000000000000000052,00000000000000000051,00000000000000000050,00000000000000000049,00000000000000000048,00000000000000000047,00000000000000000046,00000000000000000045,00000000000000000044,00000000000000000043,00000000000000000042,00000000000000000041,00000000000000000040,00000000000000000039,00000000000000000038,00000000000000000037,00000000000000000036,00000000000000000035,00000000000000000034,00000000000000000033,00000000000000000032,00000000000000000031,00000000000000000030,00000000000000000029,00000000000000000028,00000000000000000027,00000000000000000026,00000000000000000025,00000000000000000024,00000000000000000023,00000000000000000022,00000000000000000021,00000000000000000020,00000000000000000019,00000000000000000018,00000000000000000017,00000000000000000016,00000000000000000015,00000000000000000014,00000000000000000013,00000000000000000012,00000000000000000011,00000000000000000010,00000000000000000009,00000000000000000008,00000000000000000007,00000000000000000006,00000000000000000005,00000000000000000004,00000000000000000003,00000000000000000002 |