SQL & PL/SQL

Hello everyone,

I'm encountering a peculiar issue with the JSON_VALUE function in PL/SQL, which seems to behave differently based on the NLS language and territory settings. Here’s a brief overview of my problem:

plsql

-- Switch to American English settings
ALTER SESSION SET NLS_LANGUAGE = 'AMERICAN';
ALTER SESSION SET NLS_TERRITORY= 'AMERICA';

-- Uncomment to switch to German settings
--ALTER SESSION SET NLS_LANGUAGE = 'GERMAN';
--ALTER SESSION SET NLS_TERRITORY = 'GERMANY';

DECLARE
    v_number1 NUMBER;
    v_number2 NUMBER;
    v_json   VARCHAR2(400);
BEGIN
    v_json := '{"number":1.8}';
    
    v_number1 := JSON_VALUE(v_json, '$.number' RETURNING NUMBER);
    SELECT JSON_VALUE(v_json, '$.number' RETURNING NUMBER) INTO v_number2 FROM DUAL;
    
    DBMS_OUTPUT.PUT_LINE('Number 1: ' || v_number1);
    DBMS_OUTPUT.PUT_LINE('Number 2: ' || v_number2);
END;

--American
Number 1: 1.8
Number 2: 1.8


--German
Number 1: 
Number 2: 1,8

When using American settings, both v_number1 and v_number2 correctly display 1.8. However, with German settings, v_number1 returns NULL, while v_number2 returns 1.8. The key difference here is that for v_number1, JSON_VALUE is used as a PL/SQL function, whereas for v_number2, it's used within a SQL query.

Can anyone help clarify why this discrepancy occurs based on NLS settings, especially considering the different contexts in which JSON_VALUE is executed?

Thank you for any insights or guidance!