Thanks for the question, Kumar.
Asked: January 23, 2018 - 3:53 am UTC
Last updated: March 07, 2018 - 2:50 pm UTC
Version: Oracle 12.1.x
Viewed 1000+ times
You Asked
JSON_QUERY and JSON_VALUE not able to handle more than 1 lakh characters. I've a big report (size can change at any time) and push it to oracle as JSON and parse there. I'm getting JSON Syntax Error, may be since the JSON is truncated to 4000 bytes. Is Oracle JSON feature not suited for big data like this? Please help Tom!
PL/SQL code looks like:
V_JSON_DATA CLOB; -- Converted from BLOB,which comes as function parameter
V_SQL CLOB;
V_SQL := 'SELECT JSON_QUERY('''' || V_JSON_DATA || ''',''$.' || KEY || ''') FROM DUAL';
EXECUTE IMMEDIATE V_SQL;
and Chris said...
You're going to need to be more specific about what you're doing.
This example with a JSON document containing 100,009 characters works just fine:
declare
jdoc clob;
res varchar2(200);
begin
dbms_lob.createtemporary(jdoc,true);
jdoc := '{"v0": 0';
for i in 1 .. 1000 loop
dbms_lob.writeappend(jdoc,100,', "v' || i || '": "' || rpad('x', 90, 'x') || '"');
end loop;
dbms_lob.writeappend(jdoc,1,'}');
select json_query(jdoc, '$.v1' returning varchar2 with wrapper)
into res
from dual;
dbms_output.put_line(dbms_lob.getlength(jdoc) || '; ' || res);
dbms_lob.freetemporary(jdoc);
100009; ["xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"]
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment
I think the question refers to the fact that json_query returns a varchar2(4000) by default.
There is the RETURNING clause which allows to change the data type to varchar2 smaller than 4000, but not to CLOB, which would be necessary for large return values.
Is there an alternative to json_query? Maybe in apex_json?
You can specify the varchar2 length in the returning clause if you want:
with rws as (
select '{"attr":"valuuuuuuuuue"}' j
from dual
select json_value(j, '$.attr' returning varchar2(10)) too_short,
json_value(j, '$.attr' returning varchar2(20)) long_enough
from rws;
TOO_SHORT LONG_ENOUGH
<null> valuuuuuuuuue
Notice too_short is null because of the default "null on error" clause.
