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.

Reference:

WMSYS.WM_CONCAT Should Not Be Used For Customer Applications, It Is An Internal Function (Doc ID 1336219.1)
http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions089.htm#SQLRF30030
https://oracle-base.com/articles/misc/string-aggregation-techniques

Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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