Home » SQL & PL/SQL » SQL & PL/SQL » tab_to_string for longer texts ORA-06502-numeric or value error string
tab_to_string for longer texts ORA-06502-numeric or value error string [message #683080] Tue, 01 December 2020 04:57 Go to next message
rafalbballer
Messages: 5
Registered: December 2020
Junior Member
Dear All

I am trying to use Tab_to_string custom function but i am getting an error ORA-06502-numeric or value error string.
Probably what gets concatenated extends limit of varchar2.Can You let me know how can i solve this ?

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;
Re: tab_to_string for longer texts ORA-06502-numeric or value error string [message #683081 is a reply to message #683080] Tue, 01 December 2020 05:34 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

It works for me:
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.
You have to show us what you did and specify your Oracle db version with 4 decimals (query v$version).

Re: tab_to_string for longer texts ORA-06502-numeric or value error string [message #683084 is a reply to message #683080] Tue, 01 December 2020 06:25 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
It means result of concatenation exceeded l_string length:

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>
Change variable l_string and function return types to clob:

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>
SY.
Re: tab_to_string for longer texts ORA-06502-numeric or value error string [message #683085 is a reply to message #683080] Tue, 01 December 2020 07:14 Go to previous messageGo to next message
rafalbballer
Messages: 5
Registered: December 2020
Junior Member
I should post sample data in the first place.sorry for that

In the attached DDL script You can create tables and populate the dummy data.

At the bottom of the file there is a query which gives me problems.
If You uncomment Id filtred in first subquery You can see what results i would like to get for all records .

The business reason for this query is that the result of each line i am passing throu ETL Informatica which sends an email based on what's in the table(with all those added text strings it send pretty nice formatted email).
Only the error is holding me back.

Appreciate Your answers
  • Attachment: FORUM_DDL.txt
    (Size: 77.15KB, Downloaded 1358 times)
Re: tab_to_string for longer texts ORA-06502-numeric or value error string [message #683086 is a reply to message #683085] Tue, 01 December 2020 07:51 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Solomon gave you the reason of the error but, in your case, changing the function returned value to CLOB won't work as you concatenate the result of the function with another string.

The (best) solution will depend on your db version.

Re: tab_to_string for longer texts ORA-06502-numeric or value error string [message #683087 is a reply to message #683086] Tue, 01 December 2020 08:21 Go to previous messageGo to next message
rafalbballer
Messages: 5
Registered: December 2020
Junior Member
my db version is Oracle Database 18c Enterprise Edition Release 18.0.0.0.0
Re: tab_to_string for longer texts ORA-06502-numeric or value error string [message #683088 is a reply to message #683087] Tue, 01 December 2020 11:55 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can do it using xmlagg:
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.

[Updated on: Tue, 01 December 2020 11:58]

Report message to a moderator

Re: tab_to_string for longer texts ORA-06502-numeric or value error string [message #683108 is a reply to message #683088] Thu, 03 December 2020 07:32 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Any feedback?

Re: tab_to_string for longer texts ORA-06502-numeric or value error string [message #683210 is a reply to message #683108] Tue, 15 December 2020 04:44 Go to previous messageGo to next message
rafalbballer
Messages: 5
Registered: December 2020
Junior Member
sorry for very late reply ...
It is working as expected with no issues !

Thanks a ton Michael ! u saving my life
Re: tab_to_string for longer texts ORA-06502-numeric or value error string [message #683220 is a reply to message #683210] Wed, 16 December 2020 01:37 Go to previous message
rafalbballer
Messages: 5
Registered: December 2020
Junior Member
actually its almost there - when concatenating this one big string there are lots of :amp -i think because i have & in the link .
when i replaced this in excel the link worked as expected.

EDIT: i used replace(EmailBody,'amp;') in the last query . Is that correct approach ?
EDIT2: i would feel a bit better with solution to not including amp; rather than replacing it.
It might be that in the string i have text which ends with amp; and i would accidentally delete it

[Updated on: Wed, 16 December 2020 01:46]

Report message to a moderator

Previous Topic: Ora-06502 error for json_value (merged)
Next Topic: Mix of Cross and Full Outer Join
Goto Forum:
  


Current Time: Thu Mar 28 09:48:32 CDT 2024