ORA-01489: result of string concatenation is too long with json

Question

I have query which will fetch the output in JSON string, it works fine when i query it with one value.

But throws an error when i query for all the fields.

ORA-01489: result of string concatenation is too long

What is wrong with my query?

Query:

with ABC_ELEMETNS as
(
select '{'
 ||' "id":"'||A.PR||'"'
 ||' "de":"'||A.AA||'"'
 ||' "r":"'||A.BB||'"'
 ||' "r":"'||A.CC||'"'
 ||' "y":"'||A.DD||'"'
 ||'} ' json
from A
left join B on A.EE = B.EE
where A.EE = 12345
)
select '{"ELEMENTS":['
  ||(select listagg(json, ',')
             within group (order by 1)  
     from ABC_ELEMENTS)
  ||']}'
from DUAL;

Please show the query and the data type of the column with the json data — OldProgrammer, Jul 18 '17 at 18:04
Possible duplicate of [LISTAGG function: "result of string concatenation is too long"](https://stackoverflow.com/questions/13795220/listagg-function-result-of-string-concatenation-is-too-long) — OldProgrammer, Jul 18 '17 at 18:24
Hi guys, sorry i should have cleared this before, my above query works fine. my actually query will not have the where condition. there are many JSONs which will be pulled and i want each one in each row one by one ! – Oracle 2691 1 hour ago — Oracle 2691, Jul 19 '17 at 15:47

score 0 · Answer 1 · answered Jul 18 '17 at 18:52

0

LISTAGG function in Oracle is limited to 4000 bytes.

You can use XMLAGG and XMLELEMENT instead of LISTAGG.

This link can be useful:

https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions215.htm

answered Jul 18 '17 at 18:52

mehmet sahin

802
7
21

score 0 · Answer 2 · answered Jul 18 '17 at 18:56

0

LISTAGG cannot work, if the string length > 4000.

Not Tested, but should work

with ABC_ELEMENTS as
(
select '{'
 ||' "id":"'||A.PR||'"'
 ||' "de":"'||A.AA||'"'
 ||' "r":"'||A.BB||'"'
 ||' "r":"'||A.CC||'"'
 ||' "y":"'||A.DD||'"'
 ||'} ' json
from A
left join B on A.EE = B.EE
where A.EE = 12345
)
select '{"ELEMENTS":['
  ||
RTRIM (
     XMLAGG (XMLELEMENT (E, json, ',').EXTRACT (
                '//text()') ORDER BY 1).GetClobVal (),
     ',')
  ||']}'
from ABC_ELEMENTS;

answered Jul 18 '17 at 18:56

Sudipta Mondal

2,550
1
19
20

Hi guys, sorry i should have cleared this before, my above query works fine. my actually query will not have the where condition. there are many JSONs which will be pulled and i want each one in each row one by one ! – Oracle 2691 Jul 19 '17 at 14:08

ORA-01489: result of string concatenation is too long with json

2 Answers2