APEX_JSON.PARSE Procedure Does Not Handle Some Unicode Characters (Doc ID 2479731.1)

Last updated on DECEMBER 04, 2018

Applies to:

Symptoms

The APEX_JSON.PARSE procedure does not handle some Unicode correctly. If  trying to add a smiley using \ud83d\ude03, the parse procedure does not handle this correctly.

Using JSON_TABLE works correctly, but this does not currently support CLOBs, so this method can not be used in 12.1.

Example Test Code:

Test case using apex_json.parse:
declare
l_values apex_json.t_values;
begin
apex_json.parse(p_values => l_values, p_source => '{"content":"\ud83d\ude0e"}'); -- smiley emoji :)
dbms_output.put_line(apex_json.get_varchar2(p_path => 'content', p_values => l_values));
end;
/

Output:
��


Test case using json_table:
DECLARE
 l_content VARCHAR2(100);
BEGIN
 SELECT content
 INTO l_content
 FROM json_table('{"content":"\ud83d\ude03"}' -- emoji :)
  ,'$' COLUMNS(content VARCHAR2(100) PATH '$.content')
  );
 dbms_output.put_line(l_content);
END;
/

Output:
??



Looking at the APEX_JSON code, the private procedure LEX_VARCHAR2 in the LEX_NEXT function assumes that Unicode characters are only 4 characters long. So the LEX_VARCHAR2 tries to convert each \u code individually (ie UNISTR('\d83d') and UNISTR('\de03')) instead of combined them (ie UNISTR('\d83d\de03')). And this errors out with below:

Changes

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.