Create a custom operator with left / right sides

Question

I am starting to develop an Oracle statement with pl / sql. There are some examples in the manual, but now I am doing something.

In all the examples I've seen, this is always the case:

CREATE OPERATOR Contains BINDING (VARCHAR2, VARCHAR2) RETURN NUMBER USING text.contains; 

and we use it as in WHERE :

 SELECT * FROM MyEmployees WHERE Contains(resume, 'Oracle') = 1; 

Is it possible to create an operator that is used as =, +, ... with left and right sides?

In this case, for example:

 SELECT * FROM MyEmployees WHERE resume Contains 'Oracle' 

Thank you for your help.

EDIT: I know this is possible with PostgreSQL, but not found for Oracle
http://www.postgresql.org/docs/current/static/sql-createoperator.html

Answer 1

Excerpt from your comment on @ ajmalmhd04 answer

I really want to use my operator as inline (=, + ...)

Yes, the documentation is a bit confused about how it defines user-defined statements, especially this part:

user-defined operators act as built-in operators, such as <,> and =;

Unfortunately, this means that you can use custom operators, where (basically) you can use built-in operators, but not in the same way (e.g. operand1 OPERATOR operand2 ), you use built-in operators such as < or = . Custom operators, if you don’t look at them very closely, are different ways of calling functions, except that they (operators) can only be used in DML statements ( select , insert , etc.). You cannot directly use them in PL / SQL, only through DML. So something like this in PL / SQL will result in PLS-00548: invalid use of operator.

 if operator(<<arguments>>) = 1 then -- something end if; 

The advantages of creating and using operators if you are not involved in the development of server-based applications, extensions (data cartridges), for example, types of indexes that are not displayed to me personally. You cannot think of a situation where you can use operators and cannot use functions. However, the opposite is not true. You can look at the ability of an operator to have multiple bindings as a way of using it as an overloaded function. But on the other hand, you can easily achieve the same with packages.

Answer 2

1) create a TYPE :

 CREATE OR REPLACE TYPE number_type IS TABLE OF NUMBER; 

2) create a function:

 CREATE OR REPLACE FUNCTION near_to_num_f(A number) RETURN number_type AS l_num number_type; BEGIN SELECT num BULK COLLECT INTO l_num FROM tab1 WHERE num + 15 >= a AND num -15 <= a; RETURN l_num; END; 

3) create an operator:

 CREATE OR REPLACE OPERATOR near_to BINDING (number) RETURN number_type USING near_to_num_f; 

4) Create a sample table and paste some data:

  CREATE TABLE tab1 (num NUMBER); INSERT INTO tab1 VALUES( 90); INSERT INTO tab1 VALUES( 95); insert into tab1 values( 120); 

5) Request using:

 SELECT * FROM tab1 WHERE num IN(select * from TABLE(near_to(100))); 

6) Output:

  NUM ---------- 90 95 

More details