Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Please ask technical questions in the appropriate category. Thank you!

JSON_VALUE discrepancy between SQL and PL/SQL

Kurt GeensMay 17 2024 — edited May 17 2024

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…

Comments
Post Details
Added on May 17 2024
12 comments
231 views