Error Clause for SQL Functions and Conditions

18.4 Error Clause for SQL Functions and Conditions

Some SQL query functions and conditions for JSON data accept an optional error clause, which specifies handling for a runtime error that is raised by the function or condition. This clause and the default behavior (no error clause) are summarized here.

By default, SQL functions and conditions for JSON avoid raising runtime errors. For example, when JSON data is syntactically invalid, json_exists and json_equal return false and json_value returns NULL.

But in some cases you can also specify an error clause, which overrides the default behavior. The error handling you can specify varies, but each SQL function and condition for JSON that lets you specify error handling supports at least the ERROR ON ERROR behavior of raising an error.

The optional error clause can take these forms:

ERROR ON ERROR – Raise the error (no special handling).
NULL ON ERROR – Return NULL instead of raising the error.

Not available for json_exists.
FALSE ON ERROR – Return false instead of raising the error.

Available only for json_exists and json_equal, for which it is the default.
TRUE ON ERROR – Return true instead of raising the error.

Available only for json_exists and json_equal.
EMPTY OBJECT ON ERROR – Return an empty object ({}) instead of raising the error.

Available only for json_query.
EMPTY ARRAY ON ERROR – Return an empty array ([]) instead of raising the error.

Available only for json_query.
EMPTY ON ERROR – Same as EMPTY ARRAY ON ERROR.
DEFAULT 'literal_return_value' ON ERROR – Return the specified value instead of raising the error. The value must be a constant at query compile time.

Not available:
- For json_exists, json_equal, json_serialize, json_scalar, json_mergepatch, or a json_table column value clause that has json_exists behavior
- For json_query or a json_table column value clause that has json_query behavior
- For row-level error-handing for json_table
- When SDO_GEOMETRY is specified either as the RETURNING clause data type for json_value or as a json_table column data type

The default behavior is NULL ON ERROR, except for conditions json_exists and json_equal.

You can, however, change the default behavior for a given database session, using parameter JSON_BEHAVIOR. It affects only the error handlers for SQL operators that have NULL ON ERROR as the default behavior. This means that it does not affect SQL conditions json_exists and json_equal, or SQL function json_table for columns that use keyword EXISTS (they have json_exists semantics). It affects only functions json_value, json_query, and json_table without EXISTS.

The value you give to JSON_BEHAVIOR specifies the default behavior to use for the current session, as follows:

ON_ERROR:ERROR — ERROR ON ERROR behavior is the session default.
ON_ERROR:NULL — NULL ON ERROR behavior is the session default.

A typical use case is to set the parameter to ON_ERROR:ERROR for debugging purposes, to raise an error if path-expression evaluation finds no matching value in the queried JSON data. This is illustrated in Example 18-1.

Note:

There are two levels of error handling for json_table, corresponding to its two levels of path expressions: row and column. When present, a column error handler overrides row-level error handling. The default error handler for both levels is NULL ON ERROR.

Note:

An ON EMPTY clause overrides the behavior specified by ON ERROR for the error of trying to match a missing field.

Note:

The ON ERROR clause takes effect only for runtime errors that arise when a syntactically correct SQL/JSON path expression is matched against JSON data. A path expression that is syntactically incorrect results in a compile-time syntax error; it is not handled by the ON ERROR clause.

Example 18-1 Using Parameter JSON_BEHAVIOR To Provide ERROR ON ERROR Behavior

By default, json_value returns NULL on error, which can make it hard to notice a query with errors. This query returns NULL, because the path expression, $.a, does not match a single scalar value — it matches the multiple values 1 and 2.

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

This code alters the value of parameter JSON_BEHAVIOR for the current session, causing occurrence of the same error to actually raise an error, instead of returning NULL:

ALTER SESSION SET JSON_BEHAVIOR="ON_ERROR:ERROR"

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

ORA-40470: JSON query '$.a' evaluated to multiple values.

This code resets the parameter to its default value:

ALTER SESSION SET JSON_BEHAVIOR="ON_ERROR:NULL"