How to return decimals in the right number format with Oracle JSON_VALUE

jsonoracle

The decimal separator in the JSON format is the period (.). However, when the number format of a system uses comma as a decimal separator (e.g. 10,3 instead of 10.3), the Oracle json_value expects that in order to return a valid number.

Example:

SELECT 
json_value(METADATA, '$.Amount' RETURNING NUMBER(18,6)) As AMOUNT 
FROM TABLE

This will return (null) for any JSON value that contains a decimal. I cannot save strict JSON with comma decimals. How can I then get a number back from valid JSON if Oracle cannot handle that conversion? Is there some keyword I can use in my RETURNING statement?

Note1: I have used to_number(Replace(...)) but that feels wrong and slows down some queries.

Note2: I do not want to change the number format in my session, since I want to parse the JSON and use it in a view.

Best Answer

You can use TO_NUMBER() without a REPLACE(). For example:

select to_number('12345,67', '9999999999D9999', 'NLS_NUMERIC_CHARACTERS='',.''')
from dual;

TO_NUMBER() documentation.

Related Question