PASSING Clause for SQL Functions and Conditions

18.1 PASSING Clause for SQL Functions and Conditions

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.

Note:

A SQL/JSON variable name has the syntax of a SQL identifier, but with these restrictions:

A SQL/JSON variable name never includes quote characters, even when the SQL identifier used to define it includes them.

In a PASSING clause for JSON functions and conditions, the SQL identifier that follows keyword AS can be a quoted identifier or an unquoted identifier — for example, AS "d" or AS d. This defines a SQL/JSON variable named d in the first case (no quote characters in the name), and D in the second case (implicitly uppercase). (The SQL identifier in the first case is "d", not d, and in the second case it is D, not d.)
A SQL/JSON variable name must contain only ASCII alphanumeric characters or the ASCII underscore character (decimal code 95). In addition, the name must start with a letter or an underscore character, not a digit. For example, 42 AS "2d", 42 AS "d+", and 42 AS "dã" each raise an error, the first because it starts with a numeral, the second because it contains an ASCII character that's not alphanumeric (+), and the third because it contains a non-ASCII character (ã).

A SQL/JSON variable is $ followed by a SQL/JSON variable name — for example, $d is the variable with name D.

A SQL/JSON variable, not a SQL identifier, is used in a SQL/JSON path expression. In particular, this means that quote characters are never present — you just use the name directly. For example, $.PONumber?(@ > $"d") raises an error; $.PONumber?(@ > $d) has correct syntax.

Related Topics