| 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 |
drop any operator |
| create 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 ('4242 W Main Street');
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;
/ |
| Third Operator Function |
CREATE OR REPLACE FUNCTION fn_both_contains(
stringin VARCHAR2, numbin NUMBER) RETURN NUMBER 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;
/ |
| 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;
SELECT *
FROM optab
WHERE contains(test, 42) = 1; |
| |
| Add Binding |
| Binding 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 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; |
| |
| 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; |
