declare
js json_object_t;
begin
js := json_object_t.parse('
{
"name" : "whatever",
"values" : [
{"name" : "x", "type" : "number", "value" : 2023},
{"name" : "y", "type" : "string", "value" : "some non-ASCII char àùì"}
]
}
');
end;
/
Error report -
ORA-40441: JSON syntax error
ORA-06512: at "SYS.JDOM_T", line 4
ORA-06512: at "SYS.JSON_OBJECT_T", line 86
ORA-06512: at line 4
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.
Dealing with non-ASCII characters inside JSON objects in a non-AL32UTF8 database requires definitely some extra work, depending also on the final use of the information.
For instance, if JSON's contents are consumed by a browser, the following escape method works well:
declare
js json_object_t;
begin
js := json_object_t.parse(REGEXP_REPLACE(ASCIISTR('
{
"name" : "whatever",
"values" : [
{"name" : "x", "type" : "number", "value" : 2023},
{"name" : "y", "type" : "string", "value" : "some non-ASCII char àùì"}
]
}
'), '\\([0-9A-F]{4})', '&#x\1;'));
end;
/
Escaping the characters with ASCIISTR only doesn't work because the resulting presence of the "\" characters also breaks the parser as "\" is normally used to escape JSON "reserved" characters, such as \ and ".
If the data need to be converted back from the JSON then the reverse process must be applied:
select
unistr(regexp_replace('some non-ASCII char àùì',
'&#x([0-9A-F]+);',
'\\\1',1,0)) original
from dual;
If a lossy character translation is acceptable then CONVERT function could be used.
declare
js json_object_t;
begin
js := json_object_t.parse(CONVERT('
{
"name" : "whatever",
"values" : [
{"name" : "x", "type" : "number", "value" : 2023},
{"name" : "y", "type" : "string", "value" : "some non-ASCII char àùì"}
]
}
','US7ASCII'));
end;
/
-- equivalent to:
declare
js json_object_t;
begin
js := json_object_t.parse('
{
"name" : "whatever",
"values" : [
{"name" : "x", "type" : "number", "value" : 2023},
{"name" : "y", "type" : "string", "value" : "some non-ASCII char aui"}
]
}
');
end;
/
Please note that in a database with AL32UTF8 character set, the first PL/SQL block would have been processed just fine without the need of escaping anything.
