8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

JSON_BEHAVIOR Parameter in Oracle Database 23ai

By default many JSON functions return null if they encounter a runtime error. Oracle database 23ai introduced the JSON_BEHAVIOR parameter to allow us to alter this default behaviour for our session.

Prior Releases
JSON_BEHAVIOR

Prior Releases

By default many JSON functions return null if they encounter a runtime error. The following example shows the default behaviour of the JSON_VALUE function.

SELECT json_value('banana', '$.*') as data;

DATA
--------------------------------------------------------------------------------


SQL>

We can control the handling of errors at the statement level by including an optional error clause.

SELECT json_value('banana', '$.*' error on error) as data;
*
ERROR at line 1:
ORA-40441: JSON syntax error
JZN-00078: Invalid JSON keyword 'banana' (line 1, position 1)
Help: https://docs.oracle.com/error-help/db/ora-40441/


SQL>

The error clause has a variety of settings, some of which are function-specific or condition-specific.

ERROR ON ERROR
NULL ON ERROR - Not for JSON_EXISTS.
FALSE ON ERROR – JSON_EXISTS and JSON_EQUAL only. Default.
TRUE ON ERROR – JSON_EXISTS and JSON_EQUAL only.
EMPTY OBJECT ON ERROR – JSON_QUERY only.
EMPTY ARRAY ON ERROR – JSON_QUERY only.
EMPTY ON ERROR – JSON_QUERY only.
DEFAULT 'literal_return_value' ON ERROR

JSON_BEHAVIOR

The JSON_BEHAVIOR parameter allows us to alter the behaviour of JSON functions which default to NULL ON ERROR.

We might be happy with the default of NULL ON ERROR clause, but at development time want to check our code to make sure we're getting the results we expect, not just hiding runtime errors.

When we connect to a new session we see this parameter is not set.

connect testuser1/testuser1@//localhost:1521/freepdb1

show parameter json_behavior

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
json_behavior                        string

SQL>

Without touching the parameter we call a JSON function that results in an error and we see a null value is returned.

select json_value('banana', '$.*') as data;

DATA
--------------------------------------------------------------------------------


SQL>

We set the JSON_BEHAVIOR parameter to ON_ERROR:ERROR and we see the error is reported.

alter session set json_behavior='on_error:error';

select json_value('banana', '$.*') as data;
*
ERROR at line 1:
ORA-40441: JSON syntax error
JZN-00078: Invalid JSON keyword 'banana' (line 1, position 1)
Help: https://docs.oracle.com/error-help/db/ora-40441/


SQL>

We set it to ON_ERROR:NULL, and we revert to the default behaviour.

alter session set json_behavior='on_error:null';

select json_value('banana', '$.*') as data;

DATA
--------------------------------------------------------------------------------


SQL>

We can also reset it to give the default behaviour.

alter session reset json_behavior;

select json_value('banana', '$.*') as data;

DATA
--------------------------------------------------------------------------------


SQL>

For more information see:

Error Clause for SQL Functions and Conditions
JSON Support Enhancements in Oracle Database 23ai
JSON Quick Links : Introduction, 12cR1, 12cR2, 18c, 19c, 21c, 23ai, All Articles

Hope this helps. Regards Tim...

Back to the Top.