Using LISTAGG instead of WM_CONCAT

In our application, one query attempts to retrieve more than 120000 varchar rows and concat the rows with WM_CONCAT to produce a single row. Obviously, the result would exceed maximum length of varchar in PL/SQL, which is 32767 in Oracle 11g. However, the SQL didn’t report any error and continued running without returnning any result, until it exhausted the temporary tablespace.

After a little study, I suggest that they should use LISTAGG and this function would report error immediately:

ORA-01489: result of string concatenation is too long.


WMSYS.WM_CONCAT Should Not Be Used For Customer Applications, It Is An Internal Function (Doc ID 1336219.1)


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s