In the system I have a very big message that contains are over 4000 characters encoded in varchar2 and therefore they are split into multiple messages/rows, usually 2-3, where only the last one has less than 4000 characters.
I want to concatenate them respecting the ID and the sequence.
I am using the same data as in this question and have the same problem: How do I concatenate fragmented messages of strings that are out of order in SQL
Since listagg does not work I am trying to find a workaround. The easiest way would be to do this in Python, but I really want to skip that step and deal with it in SQL since I have some other transformations that I do in SQL.
Unfortunately, listagg is not working and gives me the following error:
ORA-01489: result of string concatenation is too long 01489. 00000 - "result of string concatenation is too long" *Cause: String concatenation result is more than the maximum size. *Action: Make sure that the result is less than the maximum size.
I then googled this problem and found out that a possible solution is to use the following: LISTAGG function: "result of string concatenation is too long"
But when i apply the logic of the query using XMLAGG, I am not getting the wanted results. In fact, I am losing the possibility to group them in order of the message.
I am trying the following code but I don't seem I can make it work:
SELECT msg_id,
RTRIM(XMLAGG(XMLELEMENT(E,msg_text,',').EXTRACT('//text()') ORDER BY msg_order).GetClobVal(),',') AS LIST
FROM tablename
GROUP BY msg_id;
Even if I run this I get only the first line (before \n) of the row. I don't know if I can concatenate by the message number using XMLAGG.
How do I solve this problem? I use Oracle SQL