CREATE OR REPLACE TYPE t_varchar2_tab AS TABLE OF VARCHAR2(32767);
/

CREATE OR REPLACE FUNCTION tab_to_string (p_varchar2_tab  IN  t_varchar2_tab,
                                          p_delimiter     IN  VARCHAR2 DEFAULT ',') RETURN VARCHAR2 IS
  l_string     VARCHAR2(32767);
BEGIN
  FOR i IN p_varchar2_tab.FIRST .. p_varchar2_tab.LAST LOOP
    IF i != p_varchar2_tab.FIRST THEN
      l_string := l_string || p_delimiter;
    END IF;
    l_string := l_string || p_varchar2_tab(i);
  END LOOP;
  RETURN l_string;
END tab_to_string;

SQL> CREATE OR REPLACE TYPE t_varchar2_tab AS TABLE OF VARCHAR2(32767);
  2  /

Type created.

SQL> CREATE OR REPLACE FUNCTION tab_to_string (p_varchar2_tab  IN  t_varchar2_tab,
  2                                            p_delimiter     IN  VARCHAR2 DEFAULT ',') RETURN VARCHAR2 IS
  3    l_string     VARCHAR2(32767);
  4  BEGIN
  5    FOR i IN p_varchar2_tab.FIRST .. p_varchar2_tab.LAST LOOP
  6      IF i != p_varchar2_tab.FIRST THEN
  7        l_string := l_string || p_delimiter;
  8      END IF;
  9      l_string := l_string || p_varchar2_tab(i);
 10    END LOOP;
 11    RETURN l_string;
 12  END tab_to_string;
 13  /

Function created.

SQL> select tab_to_string(t_varchar2_tab(1,2,3)) from dual;
TAB_TO_STRING(T_VARCHAR2_TAB(1,2,3))
--------------------------------------------------------------------------------------------------------------
1,2,3

1 row selected.

SQL> select tab_to_string(t_varchar2_tab(lpad('A',20000,'A'),lpad('B',20000,'B'))) from dual;
select tab_to_string(t_varchar2_tab(lpad('A',20000,'A'),lpad('B',20000,'B'))) from dual
       *
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 1


SQL>

SQL> CREATE OR REPLACE FUNCTION tab_to_string (p_varchar2_tab  IN  t_varchar2_tab,
  2                                            p_delimiter     IN  VARCHAR2 DEFAULT ',') RETURN CLOB IS
  3    l_string     CLOB;
  4  BEGIN
  5    FOR i IN p_varchar2_tab.FIRST .. p_varchar2_tab.LAST LOOP
  6      IF i != p_varchar2_tab.FIRST THEN
  7        l_string := l_string || p_delimiter;
  8      END IF;
  9      l_string := l_string || p_varchar2_tab(i);
 10    END LOOP;
 11    RETURN l_string;
 12  END tab_to_string;
 13  /

Function created.

SQL> select tab_to_string(t_varchar2_tab(lpad('A',20000,'A'),lpad('B',20000,'B'))) from dual;

TAB_TO_STRING(T_VARCHAR2_TAB(LPAD('A',20000,'A'),LPAD('B',20000,'B')))
--------------------------------------------------------------------------------
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

SQL>

SQL> with CommentAggr as (
  2  SELECT EMAIL,AFF,ID_AFF,ID_CATEGORY,CATEGORY_NAME,ID_COMMENT,
  3  rtrim(xmlagg(xmlelement(c,USERS_EMAIL_LIST.ID||',')).extract('//text()'),',') AS IDS
  4  from TEST_USERS_EMAIL_LIST USERS_EMAIL_LIST
  5  join TEST_COMMENTS_CATEGORY CAT on CAT.id=ID_CATEGORY
  6  --where USERS_EMAIL_LIST.id=1311
  7  group by EMAIL,AFF,ID_AFF,ID_CATEGORY,CATEGORY_NAME,ID_COMMENT
  8  ),
  9   CategoryAgg as
 10  (
 11  select EMAIL,AFF,ID_AFF,ID_CATEGORY,CATEGORY_NAME,
 12  rtrim(xmlagg(xmlelement(c,IDS||',')).extract('//text()'),',') AS IDS,
 13    rtrim(xmlagg(xmlelement(c,ID_COMMENT||',')).extract('//text()'),',')||'\n' as EmailBody
 14  from CommentAggr
 15  group by EMAIL,AFF,ID_AFF,ID_CATEGORY,CATEGORY_NAME
 16  ),
 17    mails as (
 18  select EMAIL,rtrim(xmlagg(xmlelement(c,IDS||',')).extract('//text()'),',') AS IDS,
 19    xmlagg(xmlelement(c,'\n'|| AFF||' - '||CATEGORY_NAME||'\n'||'https://tetettetetetetetet/'||
 20    case when ID_CATEGORY=9999 then 'asadaasaaaaaa' else 'tttttaaaaaaaa' end||
 21    '?:embed=y&:linktarget=_self&:subscriptions=no&:alerts=no&:customViews=no&:showShareOptions'||
 22    '=false&:tabs=no&:toolbar=bottom&ID_AFFILIATE='||
 23    ID_AFF||
 24    case when ID_CATEGORY=9999 then '' else chr(38) ||'CategoryId='||ID_CATEGORY end ||
 25    '&PersonLoggedIn~na=1'
 26    ||'\nComment IDs:\n'||EmailBody||' ')).extract('//text()').getClobVal() as EmailBody
 27  from CategoryAgg
 28  group by EMAIL
 29  )
 30  select EMAIL, IDS,
 31         to_clob('Dear User \nPlease find below list of actions according to Your subscription: \n')
 32         ||
 33         EmailBody
 34         ||
 35         to_clob('\nBest Regards,\nTeam Power')  as EmailBody
 36  from mails
 37  /
EMAIL
----------------------------------------------------------------------------------------------------
IDS
----------------------------------------------------------------------------------------------------
EMAILBODY
----------------------------------------------------------------------------------------------------
test@email.com
1367,1290,1390,1172,1313,1291,1391,1368,1179,1175,1168,1310,1307,1295,1394,1372,1169,1336,1174,1316,
1294,1411,1404,1371,1182,1332,1340,1331,1288,1330,1389,1185,1178,1335,1196,1327,1402,1341,1379,1302,
1383,1306,1378,1301,1382,1305,1377,1300,1381,1304,1380,1303,1395,1374,1297,1406,1375,1298,1407,1376,
1299,1408,1373,1296,1405,1173,1314,1292,1427,1424,1419,1418,1413,1412,1392,1369,1180,1431,1430,1308,
1337,1181,1315,1293,1393,1370,1433,1434,1432,1309,1338,1366,1289,1403,1333,1194,1325,1414,1425,1420,
1193,1324,1426,1423,1417,1385,1192,1323,1429,1422,1416,1401,1190,1321,1399,1189,1320,1428,1421,1415,
1398,1188,1319,1397,1187,1318,1396,1191,1322,1400,1186,1317,1384,1170,1328,1311,1409,1387,1197,1183,
1176,1287,1329,1312,1410,1388,1184,1177,1171,1334,1195,1326,1386,1339
Dear User \nPlease find below list of actions according to Your subscription: \n\nPeru - Category1\n
https://tetettetetetetetet/tttttaaaaaaaa?:embed=y&:linktarget=_self&:subscriptions=no&:a
lerts=no&:customViews=no&:showShareOptions=false&:tabs=no&:toolbar=bottom&ID_AFF
ILIATE=160&CategoryId=1&PersonLoggedIn~na=1\nComment IDs:\n504\n \nSouth Africa SR - Categor
y2\nhttps://tetettetetetetetet/tttttaaaaaaaa?:embed=y&:linktarget=_self&:subscriptions=no&am
p;:alerts=no&:customViews=no&:showShareOptions=false&:tabs=no&:toolbar=bottom&ID
_AFFILIATE=169&CategoryId=3&PersonLoggedIn~na=1\nComment IDs:\n519\n \nUnited Kingdom - Cate
gory1\nhttps://tetettetetetetetet/tttttaaaaaaaa?:embed=y&:linktarget=_self&:subscriptions=no

<__cut__>

=1\nComment IDs:\n513\n \nPeru - Category12\nhttps://tetettetetetetetet/asadaasaaaaaa?:embed=y&amp;:
linktarget=_self&amp;:subscriptions=no&amp;:alerts=no&amp;:customViews=no&amp;:showShareOptions=fals
e&amp;:tabs=no&amp;:toolbar=bottom&amp;ID_AFFILIATE=160&amp;PersonLoggedIn~na=1\nComment IDs:\n528\n
 \nBest Regards,\nTeam Power

1 row selected.