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 |
|
Default value |
None |
Modifiable |
|
Modifiable in a PDB |
No |
Basic |
No |
This parameter is set at the session level and helps 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
. These functions, by default, return a null value in
the event of an error, which makes it difficult to identify invalid queries. Setting
the JSON_BEHAVIOR
parameter to ON_ERROR:ERROR
for
the session causes these functions to return an error for invalid queries.
Values:
-
ON_ERROR:ERROR
- Specifies thatERROR
ON
ERROR
behavior is the session default for the affected SQL functions. -
ON_ERROR:NULL
- Specifies thatNULL
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 is invalid, because the supplied JSON
data contains more than one value for property a
:
SELECT JSON_VALUE('[{a:1},{a:2}]', '$.a');
If the JSON_BEHAVIOR
parameter is not set for the
session, then NULL
ON
ERROR
is the default behavior for the JSON_VALUE
function. Therefore, the query returns null:
SQL> SHO PARAMETER JSON_BEHAVIOR
NAME TYPE VALUE
---------------- ----------- -----------------
json_behavior string
SQL> SELECT JSON_VALUE('[{a:1},{a:2}]', '$.a');
JSON_VALUE('[{A:1},{A:2}]','$.A')
---------------------------------
However, 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.