Oracle 在非unicode数据库上包含unicode字符的字符串上返回JSON |
您所在的位置：网站首页 › jsonvalueoracle数据库 › Oracle 在非unicode数据库上包含unicode字符的字符串上返回JSON |
|
问题描述
嗨，汤姆! 在具有EL8MSWIN1253字符集的数据库中，json_value() 仅返回unicode密钥之前的密钥的值。在我的示例中，json_value() 仅返回键Val1和val2的结果。当我在unicode实例上运行它时，它按预期工作。我在12.2.0.1上有类似的行为。 declare js string(500 byte) := '{"Val1":2, "Val2":"Some nonUnicode text", "Val3":"Some Greek Text ΕΛΛΗΝΙΚΑ","Val4":123,"Val5":"2020-09-29"}'; begin -- Non null result dbms_output.put_line('Val1:'||json_value(js, '$.Val1')); -- Non null result dbms_output.put_line('Val2:'||json_value(js, '$.Val2')); -- Null result dbms_output.put_line('Val3:'||json_value(js, '$.Val3')); -- Null result dbms_output.put_line('Val4:'||json_value(js, '$.Val4')); end;是否在国家字符集上使用json_value()？ 根据https://docs.oracle.com/en/database/oracle/oracle-database/12.2/adjsn/json-character-sets-and-encoding.html数据库会自动将输入字符串转换为UTF8，以便对其进行处理。 我在这里错过了什么？ 专家解答 当我们添加错误子句时，这更容易看到-- -- UTF -- SQL> select parameter, value from v$nls_parameters; PARAMETER VALUE ---------------------------------------------------------------- ------------------------------------ NLS_LANGUAGE AMERICAN NLS_TERRITORY AMERICA NLS_CURRENCY $ NLS_ISO_CURRENCY AMERICA NLS_NUMERIC_CHARACTERS ., NLS_CALENDAR GREGORIAN NLS_DATE_FORMAT DD-MON-RR NLS_DATE_LANGUAGE AMERICAN NLS_CHARACTERSET AL32UTF8 NLS_SORT BINARY NLS_TIME_FORMAT HH.MI.SSXFF AM NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR NLS_DUAL_CURRENCY $ NLS_NCHAR_CHARACTERSET AL16UTF16 NLS_COMP BINARY NLS_LENGTH_SEMANTICS BYTE NLS_NCHAR_CONV_EXCP FALSE SQL> declare 2 js string(500 byte) := '{"Val1":2, "Val2":"Some nonUnicode text", "Val3":"Some Greek Text ΕΛΛΗΝΙΚΑ","Val4":123,"Val5":"2020-09-29"}'; 3 begin 4 -- Non null result 5 dbms_output.put_line('Val1:'||json_value(js, '$.Val1' error on error)); 6 7 -- Non null result 8 dbms_output.put_line('Val2:'||json_value(js, '$.Val2' error on error)); 9 10 -- Null result 11 dbms_output.put_line('Val3:'||json_value(js, '$.Val3' error on error)); 12 13 -- Null result 14 dbms_output.put_line('Val4:'||json_value(js, '$.Val4' error on error)); 15 end; 16 / Val1:2 Val2:Some nonUnicode text Val3:Some Greek Text ΕΛΛΗΝΙΚΑ Val4:123 -- -- non UTF -- SQL> select parameter, value from v$nls_parameters; PARAMETER VALUE ---------------------------------------------------------------- ---------------------------------------------------------------- NLS_LANGUAGE AMERICAN NLS_TERRITORY AMERICA NLS_CURRENCY $ NLS_ISO_CURRENCY AMERICA NLS_NUMERIC_CHARACTERS ., NLS_CALENDAR GREGORIAN NLS_DATE_FORMAT DD-MON-RR NLS_DATE_LANGUAGE AMERICAN NLS_CHARACTERSET EL8MSWIN1253 NLS_SORT BINARY NLS_TIME_FORMAT HH.MI.SSXFF AM NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR NLS_DUAL_CURRENCY $ NLS_NCHAR_CHARACTERSET AL16UTF16 NLS_COMP BINARY NLS_LENGTH_SEMANTICS BYTE NLS_NCHAR_CONV_EXCP FALSE SQL> declare 2 js string(500 byte) := '{"Val1":2, "Val2":"Some nonUnicode text", "Val3":"Some Greek Text ΕΛΛΗΝΙΚΑ","Val4":123,"Val5":"2020-09-29"}'; 3 begin 4 -- Non null result 5 dbms_output.put_line('Val1:'||json_value(js, '$.Val1' error on error)); 6 7 -- Non null result 8 dbms_output.put_line('Val2:'||json_value(js, '$.Val2' error on error)); 9 10 -- Null result 11 dbms_output.put_line('Val3:'||json_value(js, '$.Val3' error on error)); 12 13 -- Null result 14 dbms_output.put_line('Val4:'||json_value(js, '$.Val4' error on error)); 15 end; 16 / Val1:2 Val2:Some nonUnicode text declare * ERROR at line 1: ORA-40474: invalid UTF-8 byte sequence in JSON data ORA-06512: at line 11因此，我们 * 确实 * 尝试将其转换，但不能。 底线-如今，在非UTF中运行数据库总是在寻求问题 oracleasktom 文章转载自ASKTOM，如果涉嫌侵权，请发送邮件至：[email protected]进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。 评论 |
今日新闻 |
推荐新闻 |
| CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |