Hi,
The introduction of JSON native support from 12c has come with a couple of bugs. I've already encountered 3 or 4 of them, each with a patch, but for this one I couldn't find anything related to it.
The DB version is 12.2.0.1, and here's how you can reproduce;
declare
v_json_obj json_object_t;
v_json_string varchar2(50);
begin
select json_object('id' value 'is this a bug' format json) into v_json_string from dual;
v_json_obj := json_object_t(v_json_string);
end;
/
You get:
ORA-40441: JSON syntax error
ORA-06512: at "SYS.JDOM_T", line 4
ORA-06512: at "SYS.JSON_OBJECT_T", line 28
ORA-06512: at line 8
40441. 00000 - "JSON syntax error"
*Cause: The provided JavaScript Object Notation (JSON) data had invalid
syntax and could not be parsed.
*Action: Provide JSON data with the correct syntax.
The problem comes from using the FORMAT JSON clause within the JSON_OBJECT function. Using this clause, Oracle doesn't put the string between double quotes, so when the string is passed to JSON_OBJECT_T it fails with the ORA-40441 error. If you omit the FORMAT JSON clause, it works.
But the funny thing is that it's supposed to behave exactly the same according to the documentation:
SQL Language Reference (0 Bytes)FORMAT JSON
This clause is optional and is provided for semantic clarity.
As you can see it's not only for semantic clarity.
So why using the FORMAT JSON clause then? Because if I don't I get another bug if the string has special characters! See:
select json_object('id' value 'é') from dual;
ORA-40474: séquence d'octets UTF-8 non valide dans les données JSON
Do you know if there are known bugs for these issues? I've found one for ORA-40474 (whose fix btw doesn't seem to solve the problem), but nothing really convicing for the first error.
Thanks