How to override ORDER method PL/SQL?

I found a way to do this. I can't say this is the best solution or the most elegant one, but it worked fine for my needs. Here is the code.Employee type:

CREATE OR REPLACE TYPE Employee AS OBJECT (     empno  NUMBER(4),    ename CHAR(10),    dept REF Departament,    sal NUMBER(7,2),    ORDER MEMBER FUNCTION match (other IN Employee) RETURN INTEGER   ) NOT FINAL;

Manager type:

CREATE OR REPLACE TYPE Manager UNDER Employee (  nrEmp INTEGER  );

The body for Employee type:

CREATE OR REPLACE TYPE BODY Employee AS    ORDER MEMBER FUNCTION match(other IN Employee) Return INTEGER IS    v_mng_self Manager;    v_mng_other Manager;   BEGIN       v_mng_self := TREAT(self AS Manager);      v_mng_other := TREAT(other AS Manager);      IF v_mng_self.nrEmp < v_mng_other.nrEmp THEN         RETURN -1;        ELSIF v_mng_self.nrEmp > v_mng_other.nrEmp THEN          RETURN 1;         ELSE          RETURN 0;      END IF;   END;END;

That is all you have to do if you want to compare 2 Manager objects. The ORDER method will do a type casting from Employee to Manager type. For example:

DECLARE    manager1 Manager;   manager2 Manager;BEGIN   manager1 := Manager(7823,'John',null,2000,10);   manager2 := Manager(7782,'Bob',null,3000,15);   IF manager1 < manager2 THEN     SYS.DBMS_OUTPUT.PUT_LINE('manager1 has less employees than manager2');   END IF;END;

Don't forget to set the output on before the above block of code, so you can see the displayed result.

SET SERVEROUTPUT ON;

I just had to solve the same issue and cam up with following solution:

create or replace type employee as object(   empno number( 4 ),   member function compare_internal( e employee ) return integer,   order member function compare( e employee ) return integer ) not final;/create or replace type body employee is   member function compare_internal( e employee ) return integer is      begin         return            case            when self.empno = e.empno then 0            when self.empno > e.empno then 1            when self.empno < e.empno then -1            end;      end;   order member function compare( e employee ) return integer is      begin         return compare_internal( e );      end;end;/create or replace type manager under employee(    nr_emp integer,   overriding member function compare_internal( e employee )   return integer );/create or replace type body manager is   overriding member function compare_internal( e employee ) return integer is      m manager;      r integer;      begin         if e is of ( manager ) then            m := treat( e as manager );            r :=               case                  when self.nr_emp = m.nr_emp then 0                  when self.nr_emp > m.nr_emp then 1                  when self.nr_emp < m.nr_emp then -1               end;         end if;         return r;      end;end;/

This allows overriding of order/map functions by overriding called functions.

declare  x employee;  y employee;begin  x := employee(empno => 1);  y := employee(empno => 1);  dbms_output.put_line( x.compare(y) );  -- gives 0, as both have same empno  x := manager(empno => 1, nr_emp => 2);  y := manager(empno => 1, nr_emp => 3);  dbms_output.put_line( x.compare(y) );  -- gives -1 as both have different nr_emp  x := employee(empno => 1);  y := manager(empno => 1, nr_emp => 3);  dbms_output.put_line( x.compare(y) );  -- gives 0, as both have same empno -- is that what we want?  x := manager(empno => 1, nr_emp => 3);  y := employee(empno => 1);  dbms_output.put_line( x.compare(y) );  -- gives null, y is not a managerend;