Question: I run this SQL and get
an ORA-01489 error:
SELECT
'Existing Tables: ' || LISTAGG(table_name, ',') WITHIN GROUP
(ORDER BY table_name) tablenames
FROM
user_tables;
How to get around the 4,000 character limitation causing
the ORA-01489?
Answer: The ORA-01489 error is
from using the listagg function.
The results
of listagg are constrained to the max size of
VARCHAR2(4000).
The oerr utility shows this for the
ORA-01489 error:
ORA-01489: 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.
Instead, use any of these solutions:
1 -
xmlagg function
2 - You can also use the
stragg function in lieu of listagg.
3 -
And, of course, you can build your own PL/SQL concatenation
function that returns type CLOB.
