How to extract a json value greater than 4000 bytes with Oracle 12C JSON_VALUE?
Json_value function by default will return varchar2(4000), but if you add returning clause with specified varchar2 size you can force it to return much longer values.
In your example the following query should works:
SELECT json_value(j, '$.bar' returning varchar2(32000) ERROR ON ERROR) FROM j;PS. In Oracle 12c (SQL) varchar2 is limited to 4000 bytes unless you set MAX_STRING_SIZE parameter to EXTENDED.
32767 bytes or characters if MAX_STRING_SIZE = EXTENDED