Skip to Main Content
  • Questions
  • Invalid JSON parsing by DB on non US locales...

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, IOANNIS.

Asked: June 11, 2024 - 12:01 pm UTC

Last updated: June 11, 2024 - 1:23 pm UTC

Version: 19.0.0.0

Viewed 100+ times

You Asked

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?




with LiveSQL Test Case:

and Chris said...

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.

More to Explore

PL/SQL demos

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

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library