Skip to Main Content
Forums

ORDS, SODA & JSON in the Database

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

JSON_VALUE returning varchar2 limits

a4c59b02-d67c-4f2d-ae94-4790c77486dfOct 21 2019 — edited Oct 30 2019

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: )

Comments

Post Details

Added on Oct 21 2019
2 comments
345 views