1. SQL Reference Guide
  2. SQL Query Management
  3. Primary Expressions
  4. Function Calls

Function Calls

Syntax

function_call ::= id "(" [expression ("," expression)*] ")"

Semantics

Function-call expressions are used to invoke functions, which in the current version can be built-in (system) functions only. Syntactically, a function call starts with an id which identifies the function to call by name, followed by a parenthesized list of zero or more argument expressions separated by a comma.

Each function has a signature, which specifies the sequence type of its result and a sequence type for each of its parameters. Evaluation of a function-call expression starts with the evaluation of each of its arguments. The result of each argument expression must be a subtype of the corresponding parameter type, or otherwise, it must be promotable to the parameter type. In the latter case, the argument value will actually be cast to the expected type. Finally, after type checking and any necessary promotions are done, the function's implementation is invoked with the possibly promoted argument values.

The following type promotions are currently supported:
  • INTEGER is promotable to FLOAT or DOUBLE.
  • LONG is promotable to FLOAT or DOUBLE.
  • STRING is promotable to ENUM, but the cast will succeed only if the ENUM type contains a token whose string value is the same as the input string.

See the Built-in Functions topic for the list of the supported functions in Oracle NoSQL Database.

Example 6-98 Fetch the full names of passengers who have the airport code MEL in their route from the airline baggage tracking application data 

SELECT fullname 
FROM baggageInfo bag 
WHERE EXISTS bag.bagInfo[contains($element.routing,"MEL")]

Explanation: In the BaggageInfo table, the routing field contains the codes of the airports through which the checked bag transits before reaching the destination airport. In the query above, you use a function call to the contains function. The contains function is one of the built-in functions, which indicates whether or not a search string is present inside the source string. The square brackets in the query iterates over the elements of the bagInfo array. During the iteration, the $element variable is bound to the current array element, that is, routing. Each iteration computes the expression inside the contains function on the array element. If the expression includes the string "MEL", it returns true and the element is included in the result, otherwise it is skipped. As a result, only the rows that include the MEL airport code in their routing field are displayed in the output.

Output:
{"fullname":"Zulema Martindale"}
{"fullname":"Adam Phillips"}
{"fullname":"Joanne Diaz"}
{"fullname":"Omar Harvey"}
{"fullname":"Zina Christenson"}