1

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?

4

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> 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> 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> 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.

2
  • Ah, it's MULTISET UNION. Right! (checked the docs after I noticed the multiset) Dec 25 '16 at 23:12
  • @TypoCubeᵀᴹ For regular UNION, the default is the UNION DISTINCT behaviour, but that is not a valid syntax, it is just simply UNION or UNION ALL. But this is MULTISET UNION, where the default is MULTISET UNION ALL. docs.oracle.com/database/121/SQLRF/operators006.htm#SQLRF51170 - The ALL keyword instructs Oracle to return all elements that are in the two input nested tables, including duplicate values and duplicate NULL occurrences. This is the default. Dec 25 '16 at 23:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.