Re: Can I overload operators?

From: Frank Hubeny <fhubeny_at_ntsource.com>
Date: Thu, 23 Sep 1999 00:02:27 -0500
Message-ID: <37E9B463.BD0A14D9@ntsource.com>

It would appear that only one operator can be created with the name "CLASH" because of the "CREATE OR REPLACE OPERATOR" action. The second time you run the statement, you would overwrite the first definition of the operator.

However, it is perhaps worth testing this idea.

(1) Create two object types X and Y:

create or replace type x as object (one number, two number); create or replace type y as object (one number, two number);

(2) Create a table upon which a select statement using the clash operator will be attempted.

create table op (b number, c number, d number, e number); insert into op values (1,2,3,4);

(3) Create a package called cellcoverage to be used by the operator. Here I have created an overloaded clash function which uses the two different types:

create or replace package cellcoverage is

   function clash(a in x,b in number,c in number, d in number,e number) return number;

   function clash(a in y,b in number,c in number, d in number,e number) return number;
end;
/

create or replace package body cellcoverage is

   function clash(a in x,b in number,c in number, d in number,e number) return number

   is
   begin

      return 1;
   end;
   function clash(a in y,b in number,c in number, d in number,e number) return number

   is
   begin

      return 0;
   end;
end;
/

(4) Create the operators using the two sql commands provided. No errors will occur because of the "and replace" parts of the commands.

CREATE OR REPLACE OPERATOR CLASH
  BINDING(X, NUMBER, NUMBER, NUMBER, NUMBER)   RETURN NUMBER USING CELLCOVERAGE.CLASH; /
CREATE OR REPLACE OPERATOR CLASH
  BINDING(Y, NUMBER, NUMBER, NUMBER, NUMBER)   RETURN NUMBER USING CELLCOVERAGE.CLASH; /

(5) Try to display the data in column b using the clash operator that uses the Y object type which is the last one created.

SQL> set serveroutput on
SQL> declare

  2     v  y;  -- set the appropriate object type here.
  3     cursor c is
  4     select * from op where clash(v,b,c,d,e)=0;
  5     v_row c%rowtype;
  6  begin
  7     open c;
  8     fetch c into v_row;
  9     close c;
 10     if v_row.b is null then
 11        dbms_output.put_line('No information was returned.');
 12     else
 13        dbms_output.put_line(to_char(v_row.b));
 14     end if;

 15 end;
 16 /
1

PL/SQL procedure successfully completed.

Note that there is no problem using Y, because it is used in the most recently defined version of clash.

However, using object type X, I get errors because X is no longer defined as a parameter for the clash operator.

SQL> declare

  2     v  x;  -- set the appropriate object type here.
  3     cursor c is
  4     select * from op where clash(v,b,c,d,e)=0;
  5     v_row c%rowtype;
  6  begin
  7     open c;
  8     fetch c into v_row;
  9     close c;
 10     if v_row.b is null then
 11        dbms_output.put_line('No information was returned.');
 12     else
 13        dbms_output.put_line(to_char(v_row.b));
 14     end if;

 15 end;
 16 /
declare
*
ERROR at line 1:

ORA-06550: line 4, column 27:
PLS-00306: wrong number or types of arguments in call to 'CLASH'
ORA-06550: line 4, column 4:

PL/SQL: SQL Statement ignored

ORA-06550: line 3, column 11:
PLS-00341: declaration of cursor 'C' is incomplete or malformed
ORA-06550: line 5, column 10:

PL/SQL: Item ignored
ORA-06550: line 8, column 17:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 8, column 4:
PL/SQL: SQL Statement ignored
ORA-06550: line 10, column 7:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 10, column 4:
PL/SQL: Statement ignored

Frank Hubeny

gaop_at_my-deja.com wrote:

> I have two object types, X and Y. Can I create two operators with
> the same name but two different types?
>
> CREATE OR REPLACE OPERATOR CLASH
> BINDING(X, NUMBER, NUMBER, NUMBER, NUMBER)
> RETURN NUMBER USING CELLCOVERAGE.CLASH;
> /
> CREATE OR REPLACE OPERATOR CLASH
> BINDING(Y, NUMBER, NUMBER, NUMBER, NUMBER)
> RETURN NUMBER USING CELLCOVERAGE.CLASH;
> /
>
> Thanks for any help
>
> Gao Peng
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.
Received on Thu Sep 23 1999 - 00:02:27 CDT