Create user defined operator with left/right sides
operators, oracle, plsql, sql
Solution
Excerpt from your comment to @ajmalmhd04 answer
i really want to use my operator like built-in (=, + ...)
Yes, documentation is a bit confusing in the way it defines user-defined operators, especially this part:
user-defined operators act like the built-in operators such as <, >, and =;
Unfortunately, it means that you can use user-defined operators where(mostly) you can use built-in operators but not in the same way(`operand1 OPERATOR operand2` for instance) you use built-in operators, like `<` or `=`. User-defined operators, if not look at them very very closely, are different way of calling functions with the exception that they(operators) can only be used in DML statements (`select`, `insert` and so forth). You won't be able to use them in PL/SQL directly, 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 benefits of creating and using operators, if you are not involved in developing application specific server-based extensions (data cartridges), indextypes for instance, not that visible to me personally. Cannot think of a situation where you can use operators and cannot use functions. Opposite is not true, however. You may look at operator's ability 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.
Problem
I'm starting to develop an Oracle operator with pl/sql. There are some examples in the guide but now I stick on something. In all examples that I have seen, it's always like this : ``` CREATE OPERATOR Contains BINDING (VARCHAR2, VARCHAR2) RETURN NUMBER USING text.contains; ``` and we use it like this in `WHERE` clause : ``` SELECT * FROM MyEmployees WHERE Contains(resume, 'Oracle') = 1; ``` Is it possible to create an operator, which is used like `=, +, ...` with a left and a right side ? In this case like : ``` SELECT * FROM MyEmployees WHERE resume Contains 'Oracle' ``` Thanks for your help. EDIT : I know it's possible with PostgreSQL, but didn't find for Oracle http://www.postgresql.org/docs/current/static/sql-createoperator.html