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;

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.

Pretty print JSON strings in Oracle 12.2 the easy way

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

It turns out that in Oracle 12.2 you can easily get pretty printed large JSON strings using a query like this:

select json_query(large_json_value, '$' returning clob pretty) as json_clob
from some_table;

Note however that the syntax returning clob pretty is not mentioned in the documentation, where returning varchar2(n) pretty (with n <= 4000) seems to be the only valid syntax.


I really appreciated the hidden feature because now I can quickly export a bunch of JSON strings  automatically generated with views using nested JSON_ARRAYAGG and JSON_OBJECT SQL functions.