2.186 JSON_BEHAVIOR

JSON_BEHAVIOR allows you to specify various default behaviors, at the session level, for certain SQL/JSON functions and conditions.

Property Description

Parameter type

String

Syntax

JSON_BEHAVIOR = "flag:value[;flag:value]..."

There must be no spaces inside of the double quotation marks.

Default value

None

Modifiable

ALTER SESSION

Modifiable in a PDB

No

Basic

No

This parameter allows you to change the default return data type, default type-compatibility, and default error behavior during a session for certain SQL/JSON functions and conditions. This lets you enforce consistent session-level JSON processing behavior, reducing the need for explicit overriding of default behaviors within individual SQL statements.

For example, by default, the JSON_QUERY function uses lax type-compatibility when comparing data. You can use this parameter to specify that all invocations of the JSON_QUERY function in a session use strict type-compatibility, by default.

You can specify one or more of the following flags, in any order:

  • JSON_QUERY_RET_VARCHAR

    Value: TRUE or FALSE (default)

    Specifies the default return data type for the JSON_QUERY function when the RETURNING data_type clause is omitted.

    If you set this flag to TRUE, then the return data type for the JSON_QUERY function is VARCHAR2(4000), regardless of its input data type.

    The default value of FALSE causes the JSON_QUERY function to use the data type of its input for the return data type.

  • LAX_JSON_EXISTS

    Value: TRUE (default) or FALSE

    Specifies the default type-compatibility for the JSON_EXISTS condition when the TYPE(STRICT|LAX) clause is omitted.

    If you set this flag to FALSE, then the JSON_EXISTS condition will behave as if were run with TYPE(STRICT) syntax. That is, when searching for a value in an array, if an array value does not match the data type of the search value, then the condition will behave as specified by the ON ERROR clause.

    The default value of TRUE causes the JSON_EXISTS condition to behave as if it were run with the default TYPE(LAX) syntax, and it will attempt to convert array values to the search value data type, when necessary.

  • LAX_JSON_QUERY

    Value: TRUE (default) or FALSE

    Specifies the default type-compatibility for the JSON_QUERY function when the TYPE(STRICT|LAX) clause is omitted.

    If you set this flag to FALSE, then the JSON_QUERY function behaves as if it were run with TYPE(STRICT) syntax. That is, when searching for a value in an array, if an array value does not match the data type of the search value, then the function will behave as specified by the ON ERROR clause.

    The default value of TRUE causes the JSON_QUERY function to behave as if it were run with the default TYPE(LAX) syntax, and it will attempt to convert array values to the search value data type, when necessary.

  • LAX_JSON_TABLE

    Value: TRUE (default) or FALSE

    Specifies the default type-compatibility for the JSON_TABLE function when the TYPE(STRICT|LAX) clause is omitted.

    If you set this flag to FALSE, then the JSON_TABLE function behaves as if were run with TYPE(STRICT) syntax. That is, if the value of the function's chosen key is not of the specified return data type, then the function will behave as specified by the ON ERROR clause.

    The default value of TRUE causes the JSON_TABLE function to behave as if it were run with the default TYPE(LAX) syntax, and it will attempt to convert the chosen key to the specified return data type, when necessary.

  • LAX_JSON_VALUE

    Value: TRUE (default) or FALSE

    Specifies the default type-compatibility for the JSON_VALUE function when the TYPE(STRICT|LAX) clause is omitted.

    If you set this flag to FALSE, then the JSON_VALUE function behaves as if were run with TYPE(STRICT) syntax. That is, if the function's return value is not of the specified return data type, then the function will behave as specified by the ON ERROR clause.

    The default value of TRUE causes the JSON_VALUE function to behave as if it were run with the default TYPE(LAX) syntax, and it will attempt to convert the return value to the specified return data type, when necessary.

  • ON_ERROR

    Value: ERROR or NULL (default)

    Specifies the default behavior for the functions JSON_QUERY, JSON_TABLE (without the EXISTS clause), and JSON_VALUE when the ON ERROR clause is omitted. By default, these functions return a null value in the event of an error, which can make it difficult to identify invalid queries.

    If you set this flag to ERROR, then these functions will return an error for invalid queries.

    The default value of NULL causes these functions to return a null value in the event of an error.

Examples

ALTER SESSION SET JSON_BEHAVIOR = "ON_ERROR:ERROR";

ALTER SESSION SET JSON_BEHAVIOR = "LAX_JSON_QUERY:FALSE;LAX_JSON_TABLE:FALSE;LAX_JSON_VALUE:FALSE";

Note:

This parameter is available starting with Oracle AI Database 26ai.

See Also:

Oracle AI Database SQL Language Reference for more information about the JSON_QUERY, JSON_TABLE, and JSON_VALUE functions, and the JSON_EXISTS condition