extra benefit of create operator

From: Dietmar Brueckmann <dietmar.brueckmann_at_lycos.de>
Date: Fri, 26 May 2006 12:43:12 +0200
Message-ID: <1l768350h3a7h.kjrirexgkyo0.dlg@40tude.net>

Hi,

onetime I got the hint to create a operator in Oracle:

  CREATE OR REPLACE OPERATOR contains
    BINDING(VARCHAR2, VARCHAR2) RETURN integer USING   schema_x.package_y.contains;

in Schema_x I have to create a package (and grant execute it)

    CREATE OR REPLACE package package_y is       function contains(pWhere varchar2, pWhat varchar2) return integer;     end package_y;
/

    CREATE OR REPLACE package body package_y is

      function contains(pWhere varchar2, pWhat varchar2) return integer is
      begin
        IF pWhere IS NULL OR pWhat IS NULL 
        or INSTR(lower(pWhere), lower(pWhat), 1, 1) = 0 THEN
          RETURN 0;
        ELSE
          RETURN 1;
        END IF;
      end;

    end package_y;
/

That's very nice, then now I can write

  select ..
  from ...
  where contains(column_z,:A_TEXT) = 1

because the same solution with the like operator   where lower(column_z) like lower('%s'||:A_TEXT||'%s') doesn't work.

The benefit of "create operator" seems me to be I didn't need to write

  select ..
  from ...
  where schema_x.package_y.contains(column_z,:A_TEXT) = 1

Now my question is,
what is the extra benefit of using operators? For shorter writing a synonym would be quite enough.

-- 
Best regards
Dietmar Brueckmann

Received on Fri May 26 2006 - 05:43:12 CDT