Oracle SQL function json_transform, SQL/JSON functions json_value and json_query, and SQL/JSON condition json_exists accept an optional PASSING clause, which binds SQL values to SQL/JSON variables for use in path expressions.

When essentially the same query or update statement is repeated, with only some literal values in its textual representation being different, that code needs to be recompiled, which can be expensive. This is especially the case if the same code runs repeatedly, and only some values in comparisons or assignments change. It's generally a good idea to instead use bind variables in place of such literal values.

Keyword PASSING is followed by one or more comma-separated SQL/JSON variable bindings, such as 42 AS "d".

Each binding is composed of (1) a SQL expression to be evaluated; (2) keyword AS; and (3) a SQL/JSON variable name.^Foot 1 The binding 42 AS "d" binds the value of expression 42 to the SQL/JSON variable named d, which can be used in a path-expression such as $.PONumber?(@ > $d).

If you use a PASSING clause together with a TYPE (STRICT) clause, then each value that's compared with a SQL/JSON variable in the path expression is compared strictly with respect to its JSON-language type, just as if the relevant "only" data-type conversion item method were applied to the value. The type used for comparison is that of the SQL/JSON variable.

For example, with TYPE (STRICT) specified, a comparison such as $.PONumber?(@ > $d) for a numeric value of variable $d is treated implicitly as if it were $.PONumber?(@.numberOnly() > $d). So these two queries behave the same: only PONumber fields whose value is numeric are considered, because the value of $d is numeric.

SELECT count(*) FROM j_purchaseorder
WHERE json_exists(data, '$.PONumber?(@.numberOnly() > $d)'
PASSING to_number(:1) AS "d");

SELECT count(*) FROM j_purchaseorder
WHERE json_exists(data, '$.PONumber?(@ > $d)'
PASSING to_number(:1) AS "d" TYPE(STRICT));

The expression to evaluate must be of data type BINARY_DOUBLE, BOOLEAN, DATE, JSON, NUMBER, TIMESTAMP, or TIMESTAMP WITH TIME ZONE, VARCHAR2, VECTOR; otherwise, an error is raised.

If the expression evaluates to a SQL NULL value, the effect depends on the SQL type of that NULL value, as follows:

• Passing NULL of SQL type JSON raises an error.

• Passing NULL of SQL type VARCHAR2 binds the variable to an empty JSON string, "".

• Passing NULL of SQL type RAW binds the variable to a zero-length JSON binary value.

• Passing NULL of any other SQL type binds the variable to a JSON null value.