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;
V
======
(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:
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
Hi Elisabeth,
the short answer is no.
Kind regards,
Flavio
Post a Comment