Multiset operators on nested tables with non scalar types I have a function, which performs multiset operation on two nested tables and returns result:

function odejmijEgz(tab typ_NT) return typ_NT is
  tab1 typ_NT := typ_NT();
  cursor c_osr_egz is select distinct o.id_osrodek oid, o.nazwa_o nazwa_o from egzaminy e
    right join osrodki o on e.id_osrodek = o.id_osrodek
    where e.id_osrodek is null;
  i number;
  begin
    for v_osr in c_osr_egz loop
      i := c_osr_egz%rowcount;
      tab1.extend;
      tab1(i) := v_osr;
    end loop;
  return tab multiset except tab1;
  end odejmijEgz; 


where types are declared in pl/sql block:

type typ_Rec is record(Id osrodki.id_osrodek%type,
                        Nazwa osrodki.nazwa_o%type);                 
type typ_NT is table of typ_Rec; 


Parameter of the function is nested table, populated with records.

When I use multiset except in function I got an error:

Error: PLS-00306: wrong number or types of arguments in call to 'MULTISET_EXCEPT_ALL'


But if I change it to multiset union it works. The tables are of the same type. Whats wrong with that?
MULTISET EXCEPT


  The element types of the nested tables must be comparable.


Comparison Conditions


  Two objects of nonscalar type are comparable if they are of the same
  named type and there is a one-to-one correspondence between their
  elements. In addition, nested tables of user-defined object types,
  even if their elements are comparable, must have MAP methods defined
  on them to be used in equality or IN conditions.


The problem is, the above can not be done with a record type. record types have only fields, and can not have member functions. Furthermore:

Record Comparisons


  Records cannot be tested natively for nullity, equality, or
  inequality.
  
  These BOOLEAN expressions are illegal:
  
  
  My_Record IS NULL 
  My_Record_1 = My_Record_2
  My_Record_1 > My_Record_2
  


This fails:

create or replace package p1 as
  type typ_Rec is record(id customers.customer_id%type, name customers.cust_first_name%type);                 
  type typ_NT is table of typ_Rec;
  function f1 (tab typ_NT) return typ_NT;
end;
/

Package created.

create or replace package body p1 as
  function f1 (tab typ_NT) return typ_NT as
    tab1 typ_NT := typ_NT();
  begin
    return tab multiset except tab1;
  end;
end;
/

Warning: Package Body created with compilation errors.

SQL&gt; show errors
Errors for PACKAGE BODY P1:

LINE/COL ERROR
-------- -----------------------------------------------------------------
5/5      PL/SQL: Statement ignored
5/12     PLS-00306: wrong number or types of arguments in call to
         'MULTISET_EXCEPT_ALL'


UNION works, because you did not specify ALL or DISTINCT, and in this case, UNION ALL is the default. UNION ALL does not need to perform comparison. But if you try UNION DISTINCT (which removes duplicates), that will also fail:

create or replace package body p1 as
  function f1 (tab typ_NT) return typ_NT as
    tab1 typ_NT := typ_NT();
  begin
    return tab multiset union distinct tab1;
  end;
end;
/

Warning: Package Body created with compilation errors.

SQL&gt; show errors
Errors for PACKAGE BODY P1:

LINE/COL ERROR
-------- -----------------------------------------------------------------
5/5      PL/SQL: Statement ignored
5/12     PLS-00306: wrong number or types of arguments in call to
         'MULTISET_UNION_DISTINCT'


You need an object type for this.

create or replace type typ_Obj as object
(
  id number(6),
  name varchar2(20)
);
/

Type created.

create or replace package p1 as
  type typ_NT is table of typ_Obj;
  function f1 (tab typ_NT) return typ_NT;
end;
/

Package created.

create or replace package body p1 as
  function f1 (tab typ_NT) return typ_NT as
    tab1 typ_NT := typ_NT();
  begin
    return tab multiset except tab1;
  end;
end;
/

Warning: Package Body created with compilation errors.

SQL&gt; show errors
Errors for PACKAGE BODY P1:

LINE/COL ERROR
-------- -----------------------------------------------------------------
5/5      PL/SQL: Statement ignored
5/12     PLS-00306: wrong number or types of arguments in call to
         'MULTISET_EXCEPT_ALL'


Notice how it still failed, because I did not define the MAP function, I did not tell the database how it can compare two user-defined objects. Now try again, this time with defining MAP (and here I assume objects have unique identifiers and two objects equal if their identifiers equal):

create or replace type typ_Obj as object
(
  id number(6),
  name varchar2(20),
  MAP MEMBER FUNCTION get_idno RETURN NUMBER
);
/

Type created.

CREATE or replace TYPE BODY typ_Obj AS
  MAP MEMBER FUNCTION get_idno RETURN NUMBER IS
  BEGIN
    RETURN id;
  END;
END;
/

Type body created.

create or replace package body p1 as
  function f1 (tab typ_NT) return typ_NT as
    tab1 typ_NT := typ_NT();
  begin
    return tab multiset except tab1;
  end;
end;
/

Package body created.


And finally it worked, no warning.