Skip Headers
Oracle® Database SQL Language Reference
11g Release 2 (11.2)

Part Number E26088-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub
SQLRF01304

CREATE OPERATOR

Purpose

Use the CREATE OPERATOR statement to create a new operator and define its bindings.

Operators can be referenced by indextypes and by SQL queries and DML statements. The operators, in turn, reference functions, packages, types, and other user-defined objects.

See Also:

Oracle Database Data Cartridge Developer's Guide and Oracle Database Concepts for a discussion of these dependencies and of operators in general

Prerequisites

To create an operator in your own schema, you must have the CREATE OPERATOR system privilege. To create an operator in another schema, you must have the CREATE ANY OPERATOR system privilege. In either case, you must also have the EXECUTE object privilege on the functions and operators referenced.

Syntax

SQLRF54228create_operator::=

Description of create_operator.gif follows
Description of the illustration create_operator.gif

SQLRF54229binding_clause::=

Description of binding_clause.gif follows
Description of the illustration binding_clause.gif

SQLRF54230implementation_clause::=

Description of implementation_clause.gif follows
Description of the illustration implementation_clause.gif

SQLRF54231context_clause::=

Description of context_clause.gif follows
Description of the illustration context_clause.gif

SQLRF54232using_function_clause::=

Description of using_function_clause.gif follows
Description of the illustration using_function_clause.gif

Semantics

SQLRF54233OR REPLACE

Specify OR REPLACE to replace the definition of the operator schema object.

SQLRF54234Restriction on Replacing an Operator You can replace the definition only if the operator has no dependent objects, such as indextypes supporting the operator.

SQLRF54235schema

Specify the schema containing the operator. If you omit schema, then the database creates the operator in your own schema.

SQLRF54236operator

Specify the name of the operator to be created. The name must satisfy the requirements listed in "Database Object Naming Rules".

SQLRF54237binding_clause

Use the binding_clause to specify one or more parameter data types (parameter_type) for binding the operator to a function. The signature of each binding—the sequence of the data types of the arguments to the corresponding function—must be unique according to the rules of overloading.

The parameter_type can itself be an object type. If it is, then you can optionally qualify it with its schema.

SQLRF54238Restriction on Binding Operators You cannot specify a parameter_type of REF, LONG, or LONG RAW.

See Also:

Oracle Database PL/SQL Language Reference for more information about overloading

SQLRF54239RETURN Clause

Specify the return data type for the binding.

The return_type can itself be an object type. If so, then you can optionally qualify it with its schema.

SQLRF54240Restriction on Binding Return Data Type You cannot specify a return_type of REF, LONG, or LONG RAW.

SQLRF54241implementation_clause

Use this clause to describe the implementation of the binding.

SQLRF54242ANCILLARY TO Clause

Use the ANCILLARY TO clause to indicate that the operator binding is ancillary to the specified primary operator binding (primary_operator). If you specify this clause, then do not specify a previous binding with just one number parameter.

SQLRF54243context_clause

Use the context_clause to describe the functional implementation of a binding that is not ancillary to a primary operator binding.

SQLRF54244WITH INDEX CONTEXT, SCAN CONTEXT Use this clause to indicate that the functional evaluation of the operator uses the index and a scan context that is specified by the implementation type.

SQLRF54245COMPUTE ANCILLARY DATA Specify COMPUTE ANCILLARY DATA to indicate that the operator binding computes ancillary data.

SQLRF54246WITH COLUMN CONTEXT Specify WITH COLUMN CONTEXT to indicate that Oracle Database should pass the column information to the functional implementation for the operator.

If you specify this clause, then the signature of the function implemented must include one extra ODCIFuncCallInfo structure.

See Also:

Oracle Database Data Cartridge Developer's Guide for instructions on using the ODCIFuncCallInfo routine

SQLRF54247using_function_clause

The using_function_clause lets you specify the function that provides the implementation for the binding. The function_name can be a standalone function, packaged function, type method, or a synonym for any of these.

If the function is subsequently dropped, then the database marks all dependent objects INVALID, including the operator. However, if you then subsequently issue an ALTER OPERATOR ... DROP BINDING statement to drop the binding, then subsequent queries and DML will revalidate the dependent objects.

Example

SQLRF54248Creating User-Defined Operators: Example This example creates a very simple functional implementation of equality and then creates an operator that uses the function. For a more complete set of examples, see Oracle Database Data Cartridge Developer's Guide.

CREATE FUNCTION eq_f(a VARCHAR2, b VARCHAR2) RETURN NUMBER AS
BEGIN
   IF a = b THEN RETURN 1;
   ELSE RETURN 0;
   END IF;
END;
/

CREATE OPERATOR eq_op
   BINDING (VARCHAR2, VARCHAR2) 
   RETURN NUMBER 
   USING eq_f; 
Reader Comment

   

Comments, corrections, and suggestions are forwarded to authors every week. By submitting, you confirm you agree to the terms and conditions. Use the OTN forums for product questions. For support or consulting, file a service request through My Oracle Support.

Hide Navigation

Quick Lookup

Database Library · Master Index · Master Glossary · Book List · Data Dictionary · SQL Keywords · Initialization Parameters · Advanced Search · Error Messages

Main Categories

This Document

New and changed documents:
RSS Feed HTML RSS Feed PDF