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)