Oracle 11.1g - Concatenating column values with large strings using wm_concat()

Question

I'm getting the following error when trying to concatenate column values in Oracle 11.1g:

ORA-06502: PL/SQL: numeric or value error: character string buffer too small

I first looked at SQL Query to concatenate column values from multiple rows in Oracle to see if I could use some of the solutions provided - but had no success.

My data looks like the following:

Table A

PID

A
B
C

Table B

PID   SEQ       NOTE_FRAGMENT

A     9999      This is the start of the note
A     9998      and this is a continuation of that note.
A     9997      Finally, this is the last part of the note for PID A.
B     9999      Note data for PID B.
C     9999      Yes
C     9998      we can 
C     9997      do 
C     9996      this work!

My query is as follows:

SELECT 
A.PID,
B.SEQ,
wm_concat(B.NOTE_FRAGMENT)
FROM A 
inner join b on A.PID = B.PID
group by A.PID, B.SEQ
order by B.SEQ

Again, I'm trying to combine all the notes for a given PID in order from greatest to least seq number. I also have a hunch that my ordering is off since I'm pretty rusty on my SQL, but I had trouble finding how since I was first getting stuck on the buffer issue.

Answer 1

I am guessing that B.SEQ is not in the group by for your query (otherwise, according to your data, you would be concatenating one row).

So, start by measuring the length of the resulting string:

SELECT A.PID, SUM(LENGTH(B.NOTE_FRAGMENT))
FROM A inner join
     b
     on A.PID = B.PID
group by A.PID
order by 3 DESC;

If the largest value is over 4000, then you can't put the results into a string.

By the way, in Oracle 11g, I would recommend listagg() instead of wm_concat().

EDIT:

Actually, Oracle is pretty powerful, so you can actually do what you want:

SELECT A.PID,
       listagg(CASE WHEN CLEN < 3500 THEN B.NOTE_FRAGMENT END), ' ') WITHIN GROUP (ORDER BY B.SEQ)
FROM (SELECT A.PID, B.SEQ,
             SUM(LENGTH(B.NOTE_FRAGMENT) + 1) OVER (PARTITION BY A.PID ORDER BY B.SEQ) as CLEN
      FROM A inner join
           b
           on A.PID = B.PID
     ) AB
GROUP BY A.PID
ORDER BY 3 DESC;

This measures the length and stops the concatenation before it gets too long.