Oracle User Defined Operators
Version 12.1.0.1

General Information
Library Note This page has been updated for Oracle release 12cR1 and shown to be compatible in the Container DB.
Note: Oracle allows developers of object-oriented applications to extend the list of built-in relational operators (for example, +, -, /, *, LIKE, AND, OR) with domain specific operators (for example, Contains, Within_Distance, Similar) called user-defined operators. A user-defined operator can be used anywhere built-in operators can be used, for example, in the select list or the where clause. Similar to built-in operators, a user-defined operator may support arguments of different types, and that it may be evaluated using an index. Similar to built-in operators, user-defined operators allow efficient content-based querying and sorting on object data.
Data Dictionary Objects
ALL_OPERATORS CDB_OPERATOR_COMMENTS OPERATOR$
ALL_OPERATOR_COMMENTS DBA_OPERATORS USER_OPERATORS
CDB_OPERATORS DBA_OPERATOR_COMMENTS USER_OPERATOR_COMMENTS
Object Privileges Privileges to tables and views granted through roles may not be valid within a procedure. See the section on AUTHID.

GRANT execute ON <object_name> TO <user_name>;
System Privileges
ALTER ANY OPERATOR CREATE OPERATOR EXECUTE ANY OPERATOR
CREATE ANY OPERATOR DROP ANY OPERATOR  
 
Create Single Bind Operator
  CREATE OR REPLACE OPERATOR <operator_name>
BINDING (data_type_in) RETURN <data_type_out> USING <function_name>;
CREATE OR REPLACE FUNCTION fn_contains(stringin VARCHAR2, valuein  VARCHAR2)
RETURN NUMBER AUTHID CURRENT_USER IS
BEGIN
  IF INSTR(stringin, valuein, 1, 1) = 0 THEN
    RETURN 0;
  END IF;

  RETURN 1;
END fn_contains;
/

CREATE OR REPLACE OPERATOR contains
BINDING(VARCHAR2, VARCHAR2) RETURN NUMBER USING fn_contains;
Simple Operator Demo Table And Data CREATE TABLE optab (
test   VARCHAR2(20));

INSERT INTO optab VALUES ('Dan Morgan');
INSERT INTO optab VALUES ('J Sweet');
INSERT INTO optab VALUES ('Liz Scott');
INSERT INTO optab VALUES ('4242 W Main Street');
INSERT INTO optab VALUES ('Capable');
COMMIT;
Single Bind Operator Demonstration SELECT *
FROM optab
WHERE contains(test, 'a') = 1;

SELECT *
FROM optab
WHERE contains(test, 'Morgan') = 1;

SELECT *
FROM optab
WHERE contains(test, ' ') = 1;
 
Multiple Binding Operator Demo
Multiple Binding Operator Creation CREATE OR REPLACE OPERATOR contains
BINDING
(data_type_in) RETURN <data_type_out>
USING <function_name>,
(data_type_in) RETURN <data_type_out>
USING <function_name>;
CREATE OR REPLACE FUNCTION fn_int_contains(numbin NUMBER, valuein NUMBER)
RETURN NUMBER AUTHID DEFINER IS
 numinstr   VARCHAR2(100);
 valinstr   VARCHAR2(100);
BEGIN
  numinstr := TO_CHAR(numbin);
  valinstr := TO_CHAR(valuein);

  IF INSTR(numinstr, valinstr, 1, 1) = 0 THEN
    RETURN 0;
  END IF;

  RETURN 1;
END fn_int_contains;
/

CREATE OR REPLACE FUNCTION fn_both_contains(stringin VARCHAR2, numbin NUMBER)
RETURN NUMBER AUTHID DEFINER IS
 numinstr   VARCHAR2(100);
BEGIN
  numinstr := TO_CHAR(numbin);

  IF INSTR(stringin, numinstr, 1, 1) = 0 THEN
    RETURN 0;
  END IF;

  RETURN 1;
END fn_both_contains;
/

CREATE OR REPLACE OPERATOR contains BINDING
(VARCHAR2, VARCHAR2) RETURN NUMBER USING fn_contains,
(NUMBER,   NUMBER)   RETURN NUMBER USING fn_int_contains;
Multiple Bindings Operator Demo Table And Data CREATE TABLE numtab (
test   NUMBER(10));

INSERT INTO numtab VALUES (000010000);
INSERT INTO numtab VALUES (213567);
INSERT INTO numtab VALUES (9835456);
INSERT INTO numtab VALUES (27334);
COMMIT;
Multiple Binding Operator Demonstration SELECT *
FROM optab
WHERE contains(test, 'a') = 1;

SELECT *
FROM optab
WHERE contains(test, 'Morgan') = 1;

SELECT *
FROM optab
WHERE contains(test, ' ') = 1;

SELECT *
FROM numtab
WHERE contains(test, 1) = 1;

SELECT *
FROM numtab
WHERE contains(test, 35) = 1;

SELECT *
FROM optab
WHERE contains(test, 42) = 1;
 
Add Binding
Bind a new function into an existing operator ALTER OPERATOR <operator_name>
ADD BINDING <input_parameters> RETURN <data_type>
USING <function_name>;
CREATE OR REPLACE FUNCTION fn_both_contains(
stringin VARCHAR2, numbin NUMBER) RETURN NUMBER AUTHID CURRENT_USER IS
 numinstr   VARCHAR2(100);
BEGIN
  numinstr := TO_CHAR(numbin);

  IF INSTR(stringin, numinstr, 1, 1) = 0 THEN
    RETURN 0;
  END IF;

  RETURN 1;
END fn_both_contains;
/

ALTER OPERATOR contains
ADD BINDING (VARCHAR2, NUMBER) RETURN NUMBER
USING fn_both_contains;

SELECT *
FROM optab
WHERE contains(test, 42) = 1;
 
Alter Operator Compile
Recompile ALTER OPERATOR <operator_name> COMPILE [REUSE SETTINGS];
ALTER OPERATOR contains COMPILE;
 
Comment Operator
Comment an operator COMMENT ON OPERATOR <operator_name> IS '<comment_text>';
COMMENT ON OPERATOR contains IS 'This is a user created operator';

set linesize 131
col comments format a60
SELECT *
FROM user_operator_comments;
 
Drop Operator
Drop Operator DROP OPERATOR <operator_name>;
DROP OPERATOR contains;
 
And Not Demo
The AndNot Operator is intended to check a string or number to see if it contains one nested element but does not contain a second nested element.
Demo Table & Data CREATE TABLE ant (
memo_fld VARCHAR2(100));

INSERT INTO ant VALUES
('The quick brown fox jumped over the lazy dogs.');

INSERT INTO ant VALUES
('I feel a lot more like I do now than I did at 11 last night.');

INSERT INTO ant VALUES
('There are three erors in this statment. True or false?');

INSERT INTO ant VALUES
('There are only 10 kinds of people in the world. Those who understand binary and those who don''t.');

INSERT INTO ant VALUES ('520-34-5678');

INSERT INTO ant VALUES ('206-555-1212');

COMMIT;
Function For String Handling CREATE OR REPLACE FUNCTION AndNotStr (
evalstr VARCHAR2,
str1in VARCHAR2,
str2in VARCHAR2)
RETURN NUMBER IS

x BOOLEAN := FALSE;
NoGood EXCEPTION;

BEGIN
   IF INSTR(evalstr, str1in, 1, 1) = 0 THEN
      RAISE NoGood;
   END IF;

   IF INSTR(evalstr, str2in, 1, 1) > 0 THEN
      RAISE NoGood;
   END IF;

   RETURN 1;

EXCEPTION
   WHEN NoGood THEN
      RETURN 0;

END AndNotStr;
/
Queries To Test String Handling Function SELECT AndNotStr('Daniel Morgan', 'an', 'or') FROM dual;
SELECT AndNotStr('Daniel Morgan', 'an', 'bb') FROM dual;
Function For Number Handling CREATE OR REPLACE FUNCTION AndNotNum (
evalnum NUMBER,
num1in NUMBER,
num2in NUMBER)
RETURN NUMBER IS

evalstr VARCHAR2(38);
num1str VARCHAR2(38);
num2str VARCHAR2(38);

NoGood  EXCEPTION;

BEGIN
   evalstr := TO_CHAR(evalnum);
   num1str := TO_CHAR(num1in);
   num2str := TO_CHAR(num2in);

   IF INSTR(evalstr, num1str, 1, 1) = 0 THEN
      RAISE NoGood;
   END IF;

   IF INSTR(evalstr, num2str, 1, 1) > 0 THEN
      RAISE NoGood;
   END IF;

   RETURN 1;

EXCEPTION
   WHEN NoGood THEN
      RETURN 0;

END AndNotNum;
/
Queries To Test Number Handling Function SELECT AndNotNum(1003402, 34,10) FROM dual;
SELECT AndNotNum(1003402, 34,11) FROM dual;
AndNot Operator CREATE OR REPLACE OPERATOR AndNot
BINDING (VARCHAR2, VARCHAR2, VARCHAR2)
RETURN NUMBER USING AndNotStr,
(NUMBER, NUMBER, NUMBER)
RETURN NUMBER USING AndNotNum;
Test Operator SELECT * FROM ant WHERE andnot(memo_fld, 'dog', 'cat') = 1;
SELECT * FROM ant WHERE andnot(memo_fld, 'are', 'dog') = 1;
SELECT * FROM ant WHERE andnot(memo_fld, '10', '11') = 1;
SELECT * FROM ant WHERE andnot(memo_fld, '0', '11') = 1;

Related Topics
Built-in Operators
Functions
Types

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved