Oracle User Defined Operators
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Header
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.2 to 12.2.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Do you remember when mainframes were replaced by client-server? When client-server was replaced by n-tier architecture? The "Cloud" is a distraction ... DevOps is not. Prepare for the future.
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
What's New In 12cR1
What's New In 12cR2

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-2017 Daniel A. Morgan All Rights Reserved