ORACLE: How to parse json UTF8

Question

When parsing json, the special characters do not consider, how do I solve it?

declare 
  str       varchar2(4000) := '[{"id":"1","PRM_CODID":"R","PRM_TIPID":"REGISTRO UNICO CONTRIBUYENTE"},{"id":"2","PRM_CODID":"C","PRM_TIPID":"**CÉDULA DE IÑENTIDAD**"},{"id":"3","PRM_CODID":"P","PRM_TIPID":"PASAPORTE"}]';
  l_obj  json_list;
  l_results       json_list;
  l_tempobj       json;
   l_addr_comps    json_list;
   l_addr          json;
   ls varchar2(100);


begin
        l_obj := json_list(str);

        FOR i IN 1 .. l_obj.count
        LOOP
             l_addr := json(l_obj.get(i));
             ls := JSON_EXT.PP(l_addr, 'PRM_TIPID') ;
             Dbms_output.put_line(ls);
        END LOOP;
end;
/

Result:
"REGISTRO UNICO CONTRIBUYENTE"
"C\u00C9DULA DE I\u00D1ENTIDAD"
"PASAPORTE"
PL/SQL procedure successfully completed.

Related: [Does PL/JSON software support UTF-8 characters](https://stackoverflow.com/questions/27370260/does-pl-json-software-support-utf-8-characters) , and perhaps also [Difference between VARCHAR2(10 CHAR) and NVARCHAR2(10)](https://stackoverflow.com/questions/4508351/difference-between-varchar210-char-and-nvarchar210) — GolezTrol, Sep 01 '17 at 18:03

score 0 · Answer 1 · answered Sep 01 '17 at 19:07

0

As per my understanding Oracle provides PL/SQL DOM APIs for JSON handling.You can check the below link for more details.

Link

answered Sep 01 '17 at 19:07

nitin.sharma0180

471
3
6

score 0 · Answer 2 · answered Sep 02 '17 at 05:03

0

You can use UNISTR function:

select unistr( replace('C\u00C9DULA DE I\u00D1ENTIDAD', '\u', '\') ) as x
from dual;

X                  
-------------------
CÉDULA DE IÑENTIDAD

answered Sep 02 '17 at 05:03

krokodilko

35,300
7
55
79

ORACLE: How to parse json UTF8

2 Answers2