CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle Operators
Version 11.1
General
Data Dictionary Objects Related To Operators operator$
DBA ALL USER
dba_operator_comments all_operator_comments user_operator_comments
dba_operators all_operators user_operators
Privileges Related To Operators create any operator
create operator
drop any operator
execute any operator
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.
 
Create Operator
Create Operator CREATE OR REPLACE OPERATOR <operator_name>
BINDING (data_type_in) RETURN <data_type_out> USING <function_name>;
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>
;
 
Comment Operator
This functionality, while documented, was never implemented. The Oracle documentation is incorrect.
 
Alter Operator
Recompile ALTER OPERATOR <operator_name> COMPILE;
ALTER OPERATOR contains COMPILE;
 
Drop Operator
Drop Operator DROP OPERATOR <operator_name>;
DROP OPERATOR contains;
 
Single Binding Operator Demo
Operator Function CREATE OR REPLACE FUNCTION fn_contains(
stringin VARCHAR2, valuein  VARCHAR2) RETURN NUMBER IS

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

  RETURN 1;
END fn_contains;
/
Single Binding Operator Creation 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 ('Capable');
COMMIT;
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
Second Operator Function CREATE OR REPLACE FUNCTION fn_int_contains(
numbin NUMBER, valuein NUMBER) RETURN NUMBER 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;
/
Multiple Binding Operator Creation 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;
 
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 OPERATOR contains
BINDING(VARCHAR2, VARCHAR2) RETURN NUMBER USING fn_contains;

ALTER OPERATOR contains
ADD BINDING (NUMBER, NUMBER) RETURN NUMBER
USING fn_int_contains;
 
Add Comments
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;
 
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
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [340 users online]    © 2010 psoug.org