|Oracle® Database SQL Reference
10g Release 1 (10.1)
Part Number B10759-01
Like built-in operators, user-defined operators take a set of operands as input and return a result. However, you create them with the
OPERATOR statement, and they are identified by user-defined names. They reside in the same namespace as tables, views, types, and standalone functions.
Once you have defined a new operator, you can use it in SQL statements like any other built-in operator. For example, you can use user-defined operators in the select list of a
SELECT statement, the condition of a
WHERE clause, or in
BY clauses and
BY clauses. However, you must have
EXECUTE privilege on the operator to do so, because it is a user-defined object.
For example, if you define an operator
includes, which takes as input a text column and a keyword and returns 1 if the row contains the specified keyword, you can then write the following SQL query:
SELECT * FROM product_descriptions WHERE includes (translated_description, 'Oracle and UNIX') = 1;