Oracle 在非unicode数据库上包含unicode字符的字符串上返回JSON_VALUE

嗨，汤姆!

在具有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中运行数据库总是在寻求问题