0

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.

1

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

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

TO_NUMBER() documentation.

  • Shouldn't RETURNING NUMBER(16,8) do the job? What is its purpose if I need to convert to a number anyway? It is not like it can't know if the value is a string or a number, since in JSON the number will not be surrounded with quotes "". – Midas Jun 13 '17 at 11:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.