Oracle custom operator we create when we do not have pre build oracle operator which is needed. In some cases can Oracle custom operator also improve our code performace.
EXAMPLE:
|
1 2 3 |
SELECT * FROM detail WHERE IsNumOp(msisdn) = 1; |
Create function:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
CREATE OR REPLACE FUNCTION TEST_USER.IsNum ( p_eval VARCHAR2) RETURN NUMBER IS ln_eval NUMBER(38,2); BEGIN ln_eval := TO_NUMBER(REPLACE(p_eval , '.', ',')); RETURN 1; EXCEPTION WHEN others THEN RETURN 0; END IsNum ; |
Create Operator:
|
1 2 |
CREATE OR REPLACE OPERATOR IsNumOp BINDING(VARCHAR2) RETURN NUMBER USING IsNum; |