I have a very strangely behaving query with Oracle 12.1.0.2.0 (Enterprise edition)
SELECT c.*
,(select LISTAGG(cx.key,',') WITHIN GROUP (ORDER BY cx.key) "PREDECESSOR_IDS" from court cx WHERE cx.successor_court_id = c.id) AS predecessors_ids
FROM court c;
This fails on a particular row in our table with SQL Error: ORA-01489: result of string concatenation is too long
When I check the row, the concatenated string is 288 characters only. According to logs LISTAGG produces a VARCHAR2(4000) and should therefore only cause the error after 4000 characters.
Here's where it gets even weirder. This query works:
SELECT c.*
,(select LISTAGG(cx.key,',') WITHIN GROUP (ORDER BY cx.key) "PREDECESSOR_IDS" from court cx WHERE cx.successor_court_id = c.id) AS predecessors_ids
FROM court c
ORDER BY c.id;
i.e. the same query, but with an order-by clause. How is this possible? (I am running the query against the entire table, btw. and retrieve all rows - no paging is occurring).
More strangeness... this query also works.
SELECT
(select LISTAGG(cx.key,',') WITHIN GROUP (ORDER BY cx.key) "PREDECESSOR_IDS" from court cx WHERE cx.successor_court_id = c.id) AS predecessors_ids
FROM court c;
i.e. the same query, with c.* removed. However this also works (obviously):
SELECT c.* FROM court c;
It seems almost as if the overall size of the resultset columns contribute to the ORA-01489 error. But I find nothing in any docs suggesting anything like that.
One final weirdness. I tried the original broken query in Oracle 11.2.0.1.0 with the same data and it works fine.
Any ideas?
Thanks,
Daniel