Thursday, June 03, 2021

JSON_QUERY behavior in SQL vs PL/SQL (Oracle 12.2 only?)

Frankly speaking I can't say if this is expected behavior for JSON_QUERY but I find it somewhat inconsistent:

set serveroutput on
begin 
 dbms_output.put_line(json_query('X','$.test'));
end;
/
[] 
select json_query('X', '$.test') v from dual;
====== 
(null)

So, on Oracle 12.2 in PL/SQL the string returned is not null, whilst in SQL is null.

From a test I made later on Oracle 18c the anonymous PL/SQL block above returns null, so I am inclined to consider this a bug of Oracle 12.2.

It's also worth noting that adding NULL ON ERROR clause in Oracle 12.2 doesn't change the result, which is also another possible bug (or a variation of the same problem).

Eventually I chose to use ERROR ON ERROR then catch the exception and return null, because that should avoid the risk of inconsistent results in case the db is upgraded some time in the future.

2 comments:

Anonymous said...

Hello
My name is Elisabeth and I would like to ask if there is any Guest or Sponsored post option available on your website
I would like to post a unique and high quality article with a dofollow link inside
Please let me know all the guidelines for a perfect article

Best Regards
Elisabeth Muller
elismullermarketing@gmail.com

Byte64 said...

Hi Elisabeth,
the short answer is no.

Kind regards,
Flavio

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles