We are using DB v19.0 with the latest patches.
We are in Greece but our packages / procedures and client apps are designent to work on AMERICA NLS_TERRITORY, so everything works fine!
But in some cases, some 3rd party apps, are connecting to our database using GREECE as NLS_TERRITORY.
When then NLS_TERRITORY is GREECE, the following JSON can not be read as a number:
'{"Amount":10.1}';
This is an example of what is happening:
declare
js string(4000 byte);
begin
js := '{"Amount":10.1}';
dbms_output.put_line('Territory: AMERICA');
execute immediate 'ALTER SESSION SET NLS_TERRITORY = ''AMERICA''';
dbms_output.put_line('Amount as string: ' || json_value(js, '$.Amount'));
dbms_output.put_line('Amount as number: ' || json_value(js, '$.Amount' returning number));
dbms_output.put_line('Territory: GREECE');
execute immediate 'ALTER SESSION SET NLS_TERRITORY = ''GREECE''';
dbms_output.put_line('Amount as string: ' || json_value(js, '$.Amount'));
dbms_output.put_line('Amount as number: ' || json_value(js, '$.Amount' returning number));
end;This is the output:
Territory: AMERICA
Amount as string: 10.1
Amount as number: 10.1
Territory: GREECE
Amount as string: 10.1
Amount as number:
As far as I understand, JSON number format is a standard for all countries / locales, so I assume that I am facing an DB bug.
Is there any version that solves it?
Yep, I see this on 19.22. Though it's fixed in Oracle Database 23ai:
select banner from v$version;
/*
BANNER
---------------------------------------------------------------------------------------------------------------------------------
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
*/
declare
js string(4000 byte);
begin
js := '{"Amount":10.1}';
dbms_output.put_line('Territory: AMERICA');
execute immediate 'ALTER SESSION SET NLS_TERRITORY = ''AMERICA''';
dbms_output.put_line('Amount as string: ' || json_value(js, '$.Amount'));
dbms_output.put_line('Amount as number: ' || json_value(js, '$.Amount' returning number));
dbms_output.put_line('Territory: GREECE');
execute immediate 'ALTER SESSION SET NLS_TERRITORY = ''GREECE''';
dbms_output.put_line('Amount as string: ' || json_value(js, '$.Amount'));
dbms_output.put_line('Amount as number: ' || json_value(js, '$.Amount' returning number));
end;
/
/*
Territory: AMERICA
Amount as string: 10.1
Amount as number: 10.1
Territory: GREECE
Amount as string: 10.1
Amount as number: 10,1
*/There are a few bugs related to this issue; the ones I found were all fixed in earlier releases though.
So either there's been a regression or it's caused by another issue. Either way, speak with support to get a fix.