Oracle8i SQL Reference
Release 2 (8.1.6)

A76989-01

Library

Product

Contents

Index

Prev Up Next

SQL Statements (continued), 15 of 20


CREATE OPERATOR

Syntax


binding_clause::=


implementation_clause::=


context_clause::=


using_clause::=


Purpose

To create a new operator and define its bindings.

Operators can be referenced by indextypes and by DML and query SQL statements. The operators, in turn, reference functions, packages, types, and other user-defined objects. For a discussion of these dependencies, and of operators in general, see Oracle8i Data Cartridge Developer's Guide and Oracle8i Concepts.

Prerequisites

To create an operator in your own schema, you must have 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 EXECUTE privilege on the functions and operators referenced.

Keywords and Parameters

OR REPLACE 

replaces the definition of the operator schema object.

Restriction: You can replace the definition only if the operator has no dependent objects (for example, indextypes supporting the operator). 

schema 

is the schema containing the operator. If you omit schema, Oracle assumes the operator is in your own schema. 

operator 

is the name of the operator to be created.  

binding_clause 

specifies one or more parameter datatypes (parameter_type) for binding the operator to a function. The signature of each binding (that is, the sequence of the datatypes 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, you can optionally qualify it with its schema.

Restriction: You cannot specify a parameter_type of REF, LONG, or LONG RAW.

See Also: PL/SQL User's Guide and Reference for more information about overloading. 

RETURN 

specifies the return datatype (return_type) for the binding.

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

Restriction: You cannot specify a return_type of REF, LONG, or LONG RAW

implementation_clause 

 

ANCILLARY TO primary_operator 

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

 

context_clause 

specifies the name of the implementation type used by the functional implementation of the operator as a scan context. 

 

COMPUTE ANCILLARY DATA 

specifies that the operator binding computes ancillary data.  

using_clause 

specifies the function that provides the implementation for the binding.  

 

function_name 

is the name of the function. The function can be a standalone function, packaged function, type method, or a synonym for any of these. 

Example

This example creates an operator called MERGE in the SCOTT schema with two bindings. The first binding is for merging two VARCHAR2 values and returning a VARCHAR2 result. The second binding is for merging two geometries into a single geometry. The corresponding functional implementations for the bindings are also specified.

CREATE OPERATOR scott.merge 
BINDING (varchar2, varchar2) RETURN varchar2 
         USING text.merge, 
   (spatial.geo, spatial.geo) RETURN spatial.geo 
         USING spatial.merge; 

Prev Up Next
Oracle
Copyright © 1999 Oracle Corporation.

All Rights Reserved.

Library

Product

Contents

Index