2.184 JSON_BEHAVIOR

JSON_BEHAVIOR specifies the default ON ERROR behavior for certain SQL functions that operate on JSON data.

Property	Description
Parameter type	String
Syntax	JSON_BEHAVIOR = { ON_ERROR:ERROR | ON_ERROR:NULL }
Default value	None
Modifiable	ALTER SESSION
Modifiable in a PDB	No
Basic	No

This parameter is set at the session level and allows you to ensure that JSON queries in a session are valid. This parameter is effective only for SQL functions for which NULL ON ERROR is the default behavior: JSON_QUERY, JSON_TABLE (without the EXISTS clause), and JSON_VALUE.

Values:

• ON_ERROR:ERROR - ERROR ON ERROR behavior is the session default for the affected SQL functions.

• ON_ERROR:NULL - NULL ON ERROR behavior is the session default for the affected SQL functions. This setting is equivalent to having no setting for this parameter.

Example:

The following JSON_VALUE query will fail, because the supplied JSON data contains more than one value for property a. Because NULL ON ERROR is the default behavior for the JSON_VALUE function, by default, this query returns null:

SQL> SELECT JSON_VALUE('[{a:1},{a:2}]', '$.a');

JSON_VALUE('[{A:1},{A:2}]','$.A')
---------------------------------

If you set the JSON_BEHAVIOR parameter to ON_ERROR:ERROR for the session, then the query returns an error:

SQL> ALTER SESSION SET JSON_BEHAVIOR = 'ON_ERROR:ERROR';

Session altered.

SQL> SHO PARAMETER JSON_BEHAVIOR

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
json_behavior                        string      ON_ERROR:ERROR

SQL> SELECT JSON_VALUE('[{a:1},{a:2}]', '$.a');
SELECT JSON_VALUE('[{a:1},{a:2}]', '$.a')
                                        *
ERROR at line 1:
ORA-40470: JSON query '$.a' evaluated to multiple values.

Note:

This parameter is available starting with Oracle Database 23ai.