JSON_VALUE returning varchar2 limits
Hi there,
I'm looking at the json_value function in Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 and am seeing discrepancies with the expected output when using json_value and strings that are larger than the expected returned varchar2 type.
I have looked for similar issues but cannot find any, and cannot see anything in the documentation related to this either.
I understand that the default return type is varchar2(4000) I would assume that any value larger than that should throw an error, but it seems to be dependent on both the size of the return type and the size of the full json_value.
For example, in the code below I have a json_value function returning a string of length 5031 while using a returning clause of 1258 and 1257 respectively. I would expect both to error but instead only the second statement errors. This seems to be caused because the expected length is less than a quarter of the total length. (quarter of 5031 is 1257.75)
To prove this again I update the length of the json value to 10032 and perform the same action with the return clause of 2508 and 2507 respectively and again you can see that the first will succeed while the second fails because the return type is less that a quarter of the total json_value length, but I would again have expected both to fail.
Can you confirm if this is the expected outcome, of if there might be a limitation with the json_value function?
Thanks,
Mike
declare
l_clob clob := '{"longString": "This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long.This entire string is 5031 characters long."}';
begin
--5031/4=1257.75
dbms_output.put_line(length(json_value(l_clob, '$.longString' returning varchar2(1258) error on error)));
begin
dbms_output.put_line(length(json_value(l_clob, '$.longString' returning varchar2(1257) error on error)));
exception when others then
dbms_output.put_line(sqlerrm);
end;
l_clob := '{"longString": "This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long.This entire string is 10032 characters long."}';
--10032/4=2508
dbms_output.put_line(length(json_value(l_clob, '$.longString' returning varchar2(2508) error on error)));
begin
dbms_output.put_line(length(json_value(l_clob, '$.longString' returning varchar2(2507) error on error)));
exception when others then
dbms_output.put_line(sqlerrm);
end;
l_clob := '{"longString": "8 "}';
dbms_output.put_line(length(json_value(l_clob, '$.longString' returning varchar2(2) error on error)));
begin
dbms_output.put_line(length(json_value(l_clob, '$.longString' returning varchar2(1) error on error)));
exception when others then
dbms_output.put_line(sqlerrm);
end;
end;
/
Output from the block is
5031 ORA-40478: output value too large (maximum: ) 10032 ORA-40478: output value too large (maximum: ) 8 ORA-40478: output value too large (maximum: )
