Hi All,
Have a question regarding JSON response from SQL when it returns no records or null. I have generated the json response and it works fine when records are present in the query. Use the RETURNING clause to implement it. Only issue is when no records, want to return an empty json response. (empty blob)…json response like {}
I saw this article from Oracle
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/adjsn/generation.html#GUID-C0F8F837-EE36-4EDD-9261-6E8A9245906C
Result Returned by SQL/JSON Generation Functions
The generated JSON data is returned from the function as a SQL VARCHAR2 value, whose size can be controlled by the optional RETURNING clause. For the aggregate SQL functions (json_objectagg and json_arrayagg), you can also specify CLOB as the SQL data type in the RETURNING clause.
JSON values within the returned data are derived from SQL values in the input as follows:
A SQL number is converted to a JSON number.
A non-NULL and non-number SQL value is converted to a JSON string.
A SQL NULL value is handled by the optional NULL-handling clause.
I am not sure how to use the NULL-handling clause mentioned above. It would have been nice if Oracle starts providing examples to support their docs. Please advise.