Database version = 19.17.
I'm trying to understand why a specific json_value call gives me a result in SQL while in PL/SQL it does not.
The problem arose while parsing data that contains a reverse questionmark (ascii 191). The reverse questionmark is part of the string because the data is copied over a database link from an MSWIN1252 database to a ISO8859P15 database. The original text contained a dash (asci 150) which is not supported in ISO8859P15, and therefore converted to the reverse questionmark. The json_value call is executed after storing the converted data in a local table.
When executing the json_value call in sql, the function behaves as expected:
select json_value( '[{"DEPROCE":"SPWP99999","DEPERIN":"JDOE","DEPREDT":"2024-05-16T00:00:00","DEPRETY":"XX"},{"DEPRODS":"SPWP99999 - RFC''S BLA ¿ BLABLA","DAG":"donderdag","DATUM":"16-05-2024"}]'
, '$[*].DEPERIN')
from dual;
Result = JDOE
However, executing the same json_value call in pl/sql, something goes wrong.
declare
v_json varchar2(4000) := '[{"DEPROCE":"SPWP99999","DEPERIN":"JDOE","DEPREDT":"2024-05-16T00:00:00","DEPRETY":"XX"},{"DEPRODS":"SPWP99999 - RFC''S BLA ¿ BLABLA","DAG":"donderdag","DATUM":"16-05-2024"}]';
begin
dbms_output.put_line('"' || json_value(v_json, '$[*].DEPERIN') || '"');
end;
Result = “”
As far as I know, the reverse questionmark has no specific meaning in JSON.
Does anyone have a clue why this is happening? Feels like a bug, but maybe I'm missing something…