Skip to Main Content
  • Questions
  • JSON_VALUE() and JSON_TABLE(...COLUMNS...NUMBER...) honour NLS_NUMERIC_CHARACTERS while they shouldn't.

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Jochen.

Asked: September 06, 2016 - 4:13 pm UTC

Last updated: January 22, 2021 - 10:41 am UTC

Version: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

Viewed 10K+ times! This question is

You Asked

The following shows that the same (JSON) table data and the same queries yield different results when different NLS_NUMERIC_CHARACTERS are used.
The above sentence is no surprise, obviously, but in this case I consider the behavior to be wrong; Please explain why it isn't.

Prepare the data
drop table j_nls;

CREATE TABLE j_nls(
  id          NUMBER(10),
  jsondoc     CLOB CONSTRAINT ensure_json CHECK (jsondoc IS JSON)
);


INSERT INTO j_nls VALUES(
160000,
'{ "IDNumber"      : 160000,
   "Description"   : "Just a wide ID",
   "DecimalNumber" : 160000}'
);

INSERT INTO j_nls VALUES(
1601,
'{ "IDNumber"      : 1601,
   "Description"   : "Simple Integer",
   "DecimalNumber" : 1601}'
);

INSERT INTO j_nls VALUES(
1602,
'{ "IDNumber"      : 1602,
   "Description"   : "One decimal",
   "DecimalNumber" : 1602.1}'
);

INSERT INTO j_nls VALUES(
1603,
'{ "IDNumber"      : 1603,
   "Description"   : "Three decimals",
   "DecimalNumber" : 1603.123}'
);

COMMIT;




Set NLS_NUMERIC_CHARACTERS to the "European" settings: I want to see a decimal comma in my results.
ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ",.";


Use simple dot notation to look at the JSON data:
select j.id, j.jsondoc.IDNumber, j.jsondoc.Description, j.jsondoc.DecimalNumber from j_nls j;

=> All JSON fields as string (left aligned, in SQL developer). OK.

Use JSON_VALUE() to look at the JSON data:
SELECT id,
       json_value(jsondoc, '$.IDNumber')      AS J_ID,
       json_value(jsondoc, '$.Description')   AS J_DESC,
       json_value(jsondoc, '$.DecimalNumber') AS J_DECIMAL
FROM j_nls;

=> All JSON fields as string (left aligned, in SQL developer). OK.

Use JSON_VALUE() to return one field (that has no decimal numbers) as a number.
SELECT id,
       json_value(jsondoc, '$.IDNumber' RETURNING NUMBER) AS J_ID,
       json_value(jsondoc, '$.Description')               AS J_DESC,
       json_value(jsondoc, '$.DecimalNumber')             AS J_DECIMAL
FROM j_nls;

=> J_ID is right aligned (in SQL developer) => it is indeed a number. OK.

Use JSON_VALUE() to return the DecimalNumber field/key/column as numbers.
SELECT id,
       json_value(jsondoc, '$.IDNumber' RETURNING NUMBER)      AS J_ID,
       json_value(jsondoc, '$.Description')                    AS J_DESC,
       json_value(jsondoc, '$.DecimalNumber' RETURNING NUMBER) AS J_DECIMAL
FROM j_nls;

Hmm... J_DECIMAL is null for "One decimal" and "Three decimals"
=> not the correct result :(

Try to do some calculations
with src as (
  SELECT id,
         json_value(jsondoc, '$.IDNumber' RETURNING NUMBER)      AS J_ID,
         json_value(jsondoc, '$.Description')                    AS J_DESC,
         json_value(jsondoc, '$.DecimalNumber' RETURNING NUMBER) AS J_DECIMAL
  FROM j_nls)
select ID, J_ID, J_DESC, J_DECIMAL, J_DECIMAL/2, J_DECIMAL*1000 from src;

=> So both non-integer values are replaced by SQL NULL values :(


Let's try JSON_TABLE() instead of JSON_VALUE(), returning everything as strings:
SELECT j.id, jt.J_ID, jt.J_DESC, jt.J_DECIMAL
FROM j_nls j,
     json_table(j.jsondoc, '$' COLUMNS (
          J_ID      VARCHAR2(300 CHAR) PATH '$.IDNumber',
          J_DESC    VARCHAR2(300 CHAR) PATH '$.Description',
          J_DECIMAL VARCHAR2(300 CHAR) PATH '$.DecimalNumber'
     )) jt;

=> All JSON fields as string (left aligned). OK.

JSON_TABLE with the first, non-decimal numeric column, just like before
SELECT j.id, jt.J_ID, jt.J_DESC, jt.J_DECIMAL
FROM j_nls j,
     json_table(j.jsondoc, '$' COLUMNS (
          J_ID      NUMBER(10)         PATH '$.IDNumber',
          J_DESC    VARCHAR2(300 CHAR) PATH '$.Description',
          J_DECIMAL VARCHAR2(300 CHAR) PATH '$.DecimalNumber'
     )) jt;

=> J_ID is right aligned (in SQL Developer) => it is indeed a number

And now using JSON_TABLE() to return the DeciamlNumber as a number:
SELECT j.id, jt.J_ID, jt.J_DESC, jt.J_DECIMAL
FROM j_nls j,
     json_table(j.jsondoc, '$' COLUMNS (
          J_ID      NUMBER(10)         PATH '$.IDNumber',
          J_DESC    VARCHAR2(300 CHAR) PATH '$.Description',
          J_DECIMAL NUMBER(10,6)       PATH '$.DecimalNumber'
     )) jt;

=> ORA-01722: invalid number !!!!!!!



Now let's do the same queries, but after changing NLS_NUMERIC_CHARACTERS to the american format.
ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ".,";



Use simple dot notation to look at the JSON data:
select j.id, j.jsondoc.IDNumber, j.jsondoc.Description, j.jsondoc.DecimalNumber from j_nls j;

=> All JSON fields as string (left aligned, in SQL developer). OK.

Use JSON_VALUE() to look at the JSON data:
SELECT id,
       json_value(jsondoc, '$.IDNumber')      AS J_ID,
       json_value(jsondoc, '$.Description')   AS J_DESC,
       json_value(jsondoc, '$.DecimalNumber') AS J_DECIMAL
FROM j_nls;

=> All JSON fields as string (left aligned, in SQL developer). OK.

Use JSON_VALUE() to return one field (that has no decimal numbers) as a number.
SELECT id,
       json_value(jsondoc, '$.IDNumber' RETURNING NUMBER) AS J_ID,
       json_value(jsondoc, '$.Description')               AS J_DESC,
       json_value(jsondoc, '$.DecimalNumber')             AS J_DECIMAL
FROM j_nls;

=> J_ID is right aligned (in SQL developer) => it is indeed a number. OK.

Use JSON_VALUE() to return the DecimalNumber field/key/column as numbers.
SELECT id,
       json_value(jsondoc, '$.IDNumber' RETURNING NUMBER)      AS J_ID,
       json_value(jsondoc, '$.Description')                    AS J_DESC,
       json_value(jsondoc, '$.DecimalNumber' RETURNING NUMBER) AS J_DECIMAL
FROM j_nls;

Hmm... now everything seems correct, all of a sudden :)
(Except that the numbers are displayed with a decimal dot instead of a decimal comma, what I really want.)

Try to do some calculations
with src as (
  SELECT id,
         json_value(jsondoc, '$.IDNumber' RETURNING NUMBER)      AS J_ID,
         json_value(jsondoc, '$.Description')                    AS J_DESC,
         json_value(jsondoc, '$.DecimalNumber' RETURNING NUMBER) AS J_DECIMAL
  FROM j_nls)
select ID, J_ID, J_DESC, J_DECIMAL, J_DECIMAL/2, J_DECIMAL*1000 from src;

=> All values are there, all calculations return expected results :)
(Except that the numbers are displayed with a decimal dot instead of a decimal comma, what I really want.)


Let's try JSON_TABLE() instead of JSON_VALUE(), returning everything as strings:
SELECT j.id, jt.J_ID, jt.J_DESC, jt.J_DECIMAL
FROM j_nls j,
     json_table(j.jsondoc, '$' COLUMNS (
          J_ID      VARCHAR2(300 CHAR) PATH '$.IDNumber',
          J_DESC    VARCHAR2(300 CHAR) PATH '$.Description',
          J_DECIMAL VARCHAR2(300 CHAR) PATH '$.DecimalNumber'
     )) jt;

=> All JSON fields as string (left aligned). OK.

JSON_TABLE with the first, non-decimal numeric column, just like before
SELECT j.id, jt.J_ID, jt.J_DESC, jt.J_DECIMAL
FROM j_nls j,
     json_table(j.jsondoc, '$' COLUMNS (
          J_ID      NUMBER(10)         PATH '$.IDNumber',
          J_DESC    VARCHAR2(300 CHAR) PATH '$.Description',
          J_DECIMAL VARCHAR2(300 CHAR) PATH '$.DecimalNumber'
     )) jt;

=> J_ID is right aligned (in SQL Developer) => it is indeed a number

And now using JSON_TABLE() to return the DeciamlNumber as a number:
SELECT j.id, jt.J_ID, jt.J_DESC, jt.J_DECIMAL
FROM j_nls j,
     json_table(j.jsondoc, '$' COLUMNS (
          J_ID      NUMBER(10)         PATH '$.IDNumber',
          J_DESC    VARCHAR2(300 CHAR) PATH '$.Description',
          J_DECIMAL NUMBER(10,6)       PATH '$.DecimalNumber'
     )) jt;

=> With these NLS settings there is no error :)




It is clear that with NLS_NUMERIC_CHARACTERS = ".," the results are correct:
The JSON standard describes the possibility for decimal numbers as scalar values, but apart from the digits, only the dot is allowed (as decimal separator). (Exponential notation is also allowed, but not mentioned nor used here.)
JSON data is always stored as a string (into a VARCHAR2, CLOB or BLOB), so NLS_NUMERIC_CHARACTERS has absolutely no influence when entering JSON data. Good.
Problems may arise when using the RETURNING NUMBER clause of the JSON_VALUE()-function. Instead of using the normal TO_NUMBER()-function (which honors NLS_NUMERIC_CHARACTERS), it should use a version specific for JSON data, independent of the NLS-settings. The reason for that is that the JSON standard defines what non-digit character(s) can be used to write numeric values -- only the dot -- and what its function is -- decimal separator -- (see http://www.json.org/number.gif )
The consequence of using the classic TO_NUMBER (and thereby honouring NLS_NUMERIC_CHARACTERS) is even worse when using JSON_TABLE() and defining NUMBER-columns: then it could happen that no data is returned at all.

I want to be able to use JSON_VALUE() and or JSON_TABLE() to return JSON numeric values as numbers. The resulting "fields" should act like any other numeric field in Oracle: When displaying them they must follow my preferred NLS_NUMERIC_CHARACTERS settings. When somebody else displays the same data using the same queries the numbers displayed should be according to their NLS_NUMERIC_CHARACTERS settings which may or may not be equal to mine.

and we said...

This is a bug and has been fixed in 19c.

Rating

  (7 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

How with lists in json

K Mergen, January 10, 2017 - 12:34 pm UTC

Thanks a lot for the very instructive code. I would like to exactly the same thing with the extension that I have a list of within the json. For example:
CREATE TABLE example_tab row_id number(16),(col, CLOB, constraint json_check CHECK (col IS JSON));
INSERT INTO example_tab VALUES(3,'{"data" : [1.2,2.3]}')

Now I query if NLS_NUMERIC_CHARACTER = ".," :
select row_id, pp.* from example_tab, json_table(col,'$.data[*]' columns (row_number for ordinality, col number(16,8) path '$') as pp;

And it returns as expected:
row_id, row_number, col
-----------------------
3       1           1.2
3       2           2.3


How can this be achieved if NLS_NUMERIC_CHARACTER = ",." and json_table?
Chris Saxon
January 10, 2017 - 5:29 pm UTC

I'm not sure what you're asking. If you have NLS_NUMERIC_CHARACTERS = ",.", you need to return the number as a string. Then to_number() in your select with the NLS characters reversed:

CREATE TABLE example_tab (
  row_id number(16),
  col CLOB constraint json_check CHECK (col IS JSON)
);
INSERT INTO example_tab VALUES(3,'{"data" : [1.2,2.3]}');

ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ",.";

select row_id, pp.row_number, 
       to_number(pp.col, 'fm9999999D999999', 'NLS_NUMERIC_CHARACTERS = ''.,''') col
from   example_tab, 
       json_table(col,'$.data[*]' columns (
         row_number for ordinality, col varchar2(10) path '$'
       )) as pp;

    ROW_ID ROW_NUMBER        COL
---------- ---------- ----------
         3          1        1,2
         3          2        2,3

Same thing with XML

Stew Ashton, January 10, 2017 - 8:48 pm UTC

The same issue exists with XML. XML generally uses a period as the decimal point, and Oracle will use a comma if NLS_NUMERIC_CHARACTERS says to.

Personally, I think all these NLS settings are throwbacks to the client-server days, and we still use them because we spend our time in SQL*Plus or similar tools.

- Application code should ask for the native datatypes without conversions. Then the Oracle driver will convert the native Oracle type to a native type for the application language.

- When it comes to XML or JSON data, they do not fit with the client-server paradigm; rather, they are means to transfer data between servers, be they database servers or application servers. For this reason, they should be NLS* agnostic. Ideally, datetimes would either have time zones or be normalized to UTC; code pages would be normalized to UTF8; and decimal points would be normalized to periods.

Oracle does not make the distinction between clients with specific locales and "locale-agnostic" situations like JSON and XML. it's a pity but it won't change.

What is wrong with using the AMERICAN settings? Why should you care whether a dot or a comma is displayed in SQL*Plus or Oracle SQL Developer? They are just development environments, not production user interfaces.

Production user interfaces should get native Oracle numbers converted to client language number datatypes by the Oracle driver. Then the user interface should display as desired.

If some tool like APEX used NLS settings to do the datatype conversions in the database, that would be a design flaw in APEX in my view.

"to_number" is a dangerous trap

Markus, December 07, 2017 - 3:28 pm UTC

"Tom" suggested to use to_number to solve the problem with the decimal character like this:
to_number('123.456', 'fm9999D999', 'NLS_NUMERIC_CHARACTERS = ''.,''')

However this will only work as long as the format string ('fm9999D999' in this example) matches the number. Of course you can add more '9's to the format string, but still it is not prepared for all cases: JSON allows scientific notation.
Therefore something like this will fail:
to_number('1.23E3', 'fm9999D999', 'NLS_NUMERIC_CHARACTERS = ''.,''')

Sure you could specify a format string for scientific notation, but then the normal notation would fail.

to_number can basically handle both cases (scientific and normal notation), but only if you do not specify a format string.
So these will both work, but only with decimal comma:
to_number('123,45')
to_number('1,23E3')


What we would need is this:
to_number('123.456', , 'NLS_NUMERIC_CHARACTERS = ''.,''')


But that's not allowed. When you specify the third parameter, you cannot skip the second.
These do not work either:
to_number('123.456', '', 'NLS_NUMERIC_CHARACTERS = ''.,''')
to_number('123.456', null, 'NLS_NUMERIC_CHARACTERS = ''.,''')


I have always perceived this as one of the major flaws in the Oracle database. Whenever someone writes a data import routine where floating point numbers are provided in various text formats, he will stumble across this issue. Often the problems pop up years after the coding if the database is migrated to a system with different decimal character settings.

It is shocking to see that even the JSON functions struggle with this. So in my humble opinion the original question is not an enhancement request, it is a bug report.

My solution is to use my own function instead of to_number.
It can digest any number format except those with thousands separators.

function str_to_number(p_String varchar2) return number is
  -- Get the system's decimal separator character.
  -- This is much much faster than select substr(value,1,1) into DeciSep from NLS_SESSION_PARAMETERS where parameter = 'NLS_NUMERIC_CHARACTERS'
  DeciSep constant varchar2(1) := substr(to_char(0, 'fm0gd'), -1, 1);
begin
  if p_String is null then
    return null;
  else
    begin
      -- to_number requires the system's decimal separator.
      -- The parameter 'NLS_NUMERIC_CHARACTERS = ''.,''' can only be used with a Format parameter,
      --  which is far less flexible than to_number without Format.
      return to_number(replace(replace(replace(p_String, ',', DeciSep), '.', DeciSep), ' ', ''));
    exception
      when others then
        return null;
    end;
  end if;
end;

come on, Oracle, correct it finally

DF, June 15, 2019 - 9:11 am UTC

> So in my humble opinion the original question is not an enhancement request, it is a bug report.
Absolutely true.
For example, if NLS decimal separator is not equal to "." than SDO_UTIL.FROM_GEOJSON just silently (!!!) truncates all number's content after decimal point. After this trick all geocoordinates become truncated integer numbers, can you imagine what does it mean? )) Oh, AMERICA_AMERICAN )

BTW, is it possible to use "to_number" with nsl settings inside SDO_UTIL.FROM_GEOJSON? )

Chris Saxon
June 17, 2019 - 8:45 am UTC

I'm not familiar with the GEO JSON functions, so I'm don't know about using to_number inside them.

If you want this to change in some way, raise it with support!

Behavior has changed!

Stew Ashton, October 09, 2019 - 4:07 pm UTC

Please note that database version 19c returns all the results expected by the original poster. The behavior he thought was a bug, was a bug...

Best regards,
Stew Ashton

Thanks everyone

Jochen, February 23, 2020 - 10:54 am UTC

Honestly, I forgot about this post. It was only when setting up a new phone and adding this underused E-mail account that I found all your response. Great responses, all of them.
Bottom line is there is another good reason to upgrade away from 12.
Connor McDonald
February 25, 2020 - 8:16 am UTC

Glad we could help

Bug only partially fixed!

Andres, January 22, 2021 - 7:54 am UTC

This is only fixed in SQL (19c)

This works fine:
alter session set nls_numeric_characters=", ";
select json_value('{"m":2.2}', '$.m' returning number error on error) from dual;


But if we switch to PL/SQL, the bug is still there!

declare a number;
begin
    execute immediate 'alter session set nls_numeric_characters=", "'; 
    a := json_value('{"m":2.2}', '$.m' returning number error on error);
    dbms_output.put_line('a='||a);
end;
/

This still produces ORA-01722: invalid number
The SQL and PL/SQL team seem not to be getting along with each other very well..
Chris Saxon
January 22, 2021 - 10:41 am UTC

OK, we'll look into it.

More to Explore

PL/SQL

Check out more PL/SQL tutorials on our LiveSQL tool.