0

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

OldProgrammer
  • 12,050
  • 4
  • 24
  • 45
Filip
  • 183
  • 9
  • 1
    "I get only the first line (before \n) of the row" Are you sure this isn't just how your tool displays it? If you copy the cell from your tool (e.g. SqlDeveloper) and paste it into a text editor (e.g. Notepad, Vim), do you see what you expect? – EdmCoff Jun 26 '23 at 16:12
  • @EdmCoff I am getting the same line that is displayed. I use SQL Developer. – Filip Jun 26 '23 at 16:26
  • Can you explain what is different about what you are trying and this example (uses your query, shows multiple lines)? https://dbfiddle.uk/25o8HpAr – EdmCoff Jun 26 '23 at 16:31
  • @EdmCoff Well, for example, one message contains 4000 characters, and the other contains 1000. Therefore I will get an error. From what I read, XMLAGG can avoid this length error. Example: https://dbfiddle.uk/peYL_0_B – Filip Jun 26 '23 at 16:50
  • All you are showing there is that your string literal is too long: https://dbfiddle.uk/9xxEry1_ (I changed the query to just `SELECT * FROM msg`) Is your problem that you only see the first line _when your `XMLAGG` query runs successfully_ or is your problem that you can't get `XMLAGG` to run successfully? – EdmCoff Jun 26 '23 at 17:06
  • `XMLAGG` can be used to concatenate clobs. Your query seems to work fine with clobs much larger than 4000 characters. Can you please set this up to demonstrate the actual problem you are having: https://dbfiddle.uk/7eaPoxdJ – EdmCoff Jun 26 '23 at 17:10
  • Or start from this example, which has columns >4000 and line breaks: https://dbfiddle.uk/V487aNCH – EdmCoff Jun 26 '23 at 17:17
  • @EdmCoff It appears to be a problem with printing the returns from the query. I thought my logic is bad, but thanks to your tests I managed to see that it works. Since it has +4000 characters, it only shows one row from the plain text. I had to select /*csv*/ in order to see that it works. How do I manage to change this in sql developer, so that I can see the full result of the query? – Filip Jun 26 '23 at 18:59
  • I'm not sure the best way, but do you actually need to view this data in SQL Developer? If you are just doing a sanity check on the data, CSV or copy-'n-paste doesn't seem too bad to me. Is this being displayed/reported in a downstream system for users? In most cases I would expect you can just test there. I haven't tested this, but do you see more/less if you run as a script (F5) versus a statement (ctrl-enter)? What about if you run `set long 32767` before your query? – EdmCoff Jun 26 '23 at 22:14

0 Answers0