How to Perform MULTISET Operations on Nested Table of Objects

We’ve seen the easy working of MULTISET operations on nested tables of simple types (i.e. collections of text, numbers). And we’ve seen the problem with extending MULTISET operations to nested tables of complex types (i.e. those based on OBJECTs with multiple attributes).

PL/SQL code when run with MULTISET operations on complex collections throws up the error:

PLS-00306: wrong number or types of arguments in call to 'MULTISET_<operation type>'

There is a workaround though, which lets us use MULTISET operations successfully with complex types. Here’s how.

Problem: MULTISET EXCEPT with Nested Table of OBJECTs: PLS-00306 Error

First, a recap of the error encountered when attempting to use MULTISET EXCEPT on a nested table of OBJECTs.

emp_obj is defined as an object type with attributes emp_id and emp_name, and emp_tbl_typ as a table of object emp_tbl.

SQL> CREATE TYPE emp_obj AS OBJECT (
  2      employee_id    VARCHAR2(150)
  3    , employee_name   VARCHAR2(240)
  4  )
  5  /

Type created.

SQL>
SQL> CREATE TYPE emp_tbl_typ AS TABLE OF emp_obj
  2  /

Type created.

The PL/SQL code does a MULTISET EXCEPT between two variables of type emp_tbl_typ.

SQL> DECLARE
  2
  3    v_engineers            emp_tbl_typ;
  4    v_poorlypaidemployees  emp_tbl_typ;
  5    v_wellpaidengineers    emp_tbl_typ;
  6
  7  BEGIN
  8
  9    v_engineers  := emp_tbl_typ(emp_obj('E101','Wally')
 10                              , emp_obj('E102','Ted')
 11                              , emp_obj('E104','Asok')
 12                              , emp_obj('E103','Carol'));
 13
 14    v_poorlypaidemployees := emp_tbl_typ(emp_obj('E104','Asok')
 15                              , emp_obj('E101','Wally')
 16                              , emp_obj('E108','Ratbert'));
 17
 18
 19    /* Set (well-paid engineers) =
 20                 Set (employees who are engineers)
 21       excluding Set (employees who are poorly paid) */
 22
 23    v_wellpaidengineers :=
 24      v_engineers MULTISET EXCEPT v_poorlypaidemployees;
 25
 26    IF v_wellpaidengineers IS NOT EMPTY THEN
 27
 28      FOR i IN 1..v_wellpaidengineers.COUNT LOOP
 29        dbms_output.put_line(v_wellpaidengineers(i).employee_name);
 30      END LOOP;
 31
 32    END IF;
 33
 34  END;
 35  /

One would think that this should work since the variables are of the same type, but it doesn’t:

    v_engineers MULTISET EXCEPT v_poorlypaidemployees;
    *
ERROR at line 24:
ORA-06550: line 24, column 5:
PLS-00306: wrong number or types of arguments in call to 'MULTISET_EXCEPT_ALL'
ORA-06550: line 23, column 3:
PL/SQL: Statement ignored
ORA-06550: line 29, column 51:
PLS-00302: component 'EMPLOYEE_NAME' must be declared
ORA-06550: line 29, column 7:
PL/SQL: Statement ignored

Solution: MULTISET EXCEPT with Nexted Table of OBJECTs: Success with MAP!

It turns out that for complex collections to be compared to other collections or to be used with MULTISET operators, the elements of the collection need to be sortable. To enable sorting, a single MAP order method needs to be included in the object type definition.

As below:

SQL> CREATE TYPE emp_obj AS OBJECT (
  2      employee_id    VARCHAR2(150)
  3    , employee_name   VARCHAR2(240)
  4    , MAP MEMBER FUNCTION map
  5      RETURN VARCHAR2
  6  )
  7  /

Type created.

SQL> -- MAP function to enable sorting
SQL> CREATE TYPE BODY emp_obj AS
  2    MAP MEMBER FUNCTION map
  3      RETURN VARCHAR2
  4    IS
  5    BEGIN
  6      RETURN employee_name;
  7    END;
  8  END;
  9  /

Type body created.

SQL>
SQL> CREATE TYPE emp_tbl_typ AS TABLE OF emp_obj
  2  /

Type created. 

Now for the PL/SQL that performs MULTISET EXCEPT — ta-da!

SQL> DECLARE
  2
  3    v_engineers            emp_tbl_typ;
  4    v_poorlypaidemployees  emp_tbl_typ;
  5    v_wellpaidengineers    emp_tbl_typ;
  6
  7  BEGIN
  8
  9    v_engineers  := emp_tbl_typ(emp_obj('E101','Wally')
 10                              , emp_obj('E102','Ted')
 11                              , emp_obj('E104','Asok')
 12                              , emp_obj('E103','Carol'));
 13
 14    v_poorlypaidemployees :=
 15                    emp_tbl_typ(emp_obj('E104','Asok')
 16                              , emp_obj('E101','Wally')
 17                              , emp_obj('E108','Ratbert'));
 18
 19
 20    /* Set (well-paid engineers) =
 21                 Set (employees who are engineers)
 22       excluding Set (employees who are poorly paid) */
 23
 24    v_wellpaidengineers :=
 25      v_engineers MULTISET EXCEPT v_poorlypaidemployees;
 26
 27    IF v_wellpaidengineers IS NOT EMPTY THEN
 28
 29      dbms_output.put_line('Well-paid engineers:');
 30
 31      FOR i IN 1..v_wellpaidengineers.COUNT LOOP
 32        dbms_output.put(v_wellpaidengineers(i).employee_id ||',');
 33        dbms_output.put_line(v_wellpaidengineers(i).employee_name);
 34      END LOOP;
 35
 36    END IF;
 37
 38  END;
 39  /
Well-paid engineers:
E102,Ted
E103,Carol

PL/SQL procedure successfully completed.