How to override ORDER method PL/SQL?

Question

I have to define an object type Employee that will have some attributes, then i have to define an object type Manager that will inherit the type Employee, and will have an aditional attribute called nrEmp that will contain the number of employees each manager has under his command. Also i must implement the method ORDER for the type Manager , so i can order managers by the number of employees they have. First i defined this type:

CREATE OR REPLACE TYPE Departament AS OBJECT ( 
    deptno NUMBER(2),
    dname CHAR(14)
   );

Next i defined the type Employee:

CREATE OR REPLACE TYPE Employee AS OBJECT ( 
    empno  NUMBER(4),
    ename CHAR(10),
    dept REF Departament,
    sal NUMBER(7,2)
   ) NOT FINAL;

Everything worked fine till now. Next i try to define the type Manager:

CREATE OR REPLACE TYPE Manager UNDER Employee (
  nrEmp INTEGER,
  ORDER MEMBER FUNCTION compare(m Manager) RETURN INTEGER
  );

When i do this i get the following error:

Error(1,1): PLS-00646: MAP or ORDER method must be defined in the root of the subtype hierarchy

As i understand i have to declare the method in the Employee type. But i'm not sure how do do it the right way. Couldn't find any example showing how to implement the ORDER method when inheriting. Any help would be appreciated. Thank you.

Answer 1

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;

Answer 2

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 manager

end;