MULTISET Comparisons and Map Methods

View All Scripts Login to Run Script

Script Name MULTISET Comparisons and Map Methods
Description The MULTISET operators are fantastic, declarative, set-oriented functionality for nested tables. But if you have a nested table of object types, then you will also need to provide a MAP method for that object type if your MULTISET needs to do a comparison. Which is true for pretty much every variation on MULTISET except for MULTISET UNION [ALL]. For full explanation, see my blog post: https://stevenfeuersteinonplsql.blogspot.com/2018/10/why-wont-multiset-work-for-me.html
Area PL/SQL General
Contributor Steven Feuerstein
Created Wednesday October 24, 2018

Statement 1

CREATE TABLE limbs
(
   nm        VARCHAR2 (100),
   avg_len   NUMBER
)

Table created.

Statement 2

BEGIN
   INSERT INTO limbs VALUES ('arm', 1);

   INSERT INTO limbs VALUES ('leg', 2);

   INSERT INTO limbs VALUES ('tail', 3);

   COMMIT;
END;

1 row(s) inserted.

Statement 3

CREATE OR REPLACE TYPE limb_ot AUTHID DEFINER 
   IS OBJECT 
(
   nm VARCHAR2 (100),
   avg_len NUMBER
)

Type created.

Statement 4

MULTISET UNION = MULTISET UNION ALL, which means no checking for duplicates, which means no need to compare, so no problem!

Works Just Fine

DECLARE
   TYPE limbs_t IS TABLE OF limb_ot;

   l_limbs   limbs_t;
BEGIN
     SELECT limb_ot (l.nm, l.avg_len)
       BULK COLLECT INTO l_limbs
       FROM limbs l
   ORDER BY l.nm;

   l_limbs := l_limbs MULTISET UNION l_limbs;
   DBMS_OUTPUT.put_line ('Lots of limbs! ' || l_limbs.COUNT);
END;

Statement processed.
Lots of limbs! 6

Statement 5

Again, no need to make a comparison, so we are good.

Even Works for %ROWTYPE Elements!

DECLARE
   TYPE limbs_t IS TABLE OF limbs%ROWTYPE;

   l_limbs   limbs_t;
BEGIN
     SELECT l.nm, l.avg_len
       BULK COLLECT INTO l_limbs
       FROM limbs l
   ORDER BY l.nm;

   l_limbs := l_limbs MULTISET UNION l_limbs;
   DBMS_OUTPUT.put_line ('Lots of limbs! ' || l_limbs.COUNT);
END;

Statement processed.
Lots of limbs! 6

Statement 6

Now we get an error. MULTISET EXCEPT can't do its job without comparing contents of the object type. The limb_ot object type has no map method (yet!), so it fails.

EXCEPT Requires Comparison

DECLARE
   TYPE limbs_t IS TABLE OF limb_ot;

   l_limbs   limbs_t;
BEGIN
     SELECT limb_ot (l.nm, l.avg_len)
       BULK COLLECT INTO l_limbs
       FROM limbs l
   ORDER BY l.nm;

   l_limbs := l_limbs MULTISET EXCEPT l_limbs;
   DBMS_OUTPUT.put_line ('Lots of limbs! ' || l_limbs.COUNT);
END;

ORA-06550: line 11, column 15:
PLS-00306: wrong number or types of arguments in call to 'MULTISET_EXCEPT_ALL'

More Details: https://docs.oracle.com/error-help/db/ora-06550

Statement 7

I add the DISTINCT modifier and now MULTISET UNION doesn't work.

UNION DISTINCT - Compares

DECLARE
   TYPE limbs_t IS TABLE OF limb_ot;
   l_limbs   limbs_t;
BEGIN
     SELECT limb_ot (l.nm, l.avg_len)
       BULK COLLECT INTO l_limbs
       FROM limbs l
   ORDER BY l.nm;

   l_limbs := l_limbs MULTISET UNION DISTINCT l_limbs;
   DBMS_OUTPUT.put_line ('Lots of limbs! ' || l_limbs.COUNT);
END;

ORA-06550: line 10, column 15:
PLS-00306: wrong number or types of arguments in call to 'MULTISET_UNION_DISTINCT'

More Details: https://docs.oracle.com/error-help/db/ora-06550

Statement 8

Add a Map Method!

CREATE OR REPLACE TYPE limb_ot AUTHID DEFINER 
   IS OBJECT
(
   nm VARCHAR2 (100),
   avg_len NUMBER,
   MAP MEMBER FUNCTION limb_map
      RETURN NUMBER
)

Type created.

Statement 9

It's a silly mapping algorithm - only looks at the length of the name. When you create your map method, make sure it reflects the logic of your data.

CREATE OR REPLACE TYPE BODY limb_ot
IS
   MAP MEMBER FUNCTION limb_map
      RETURN NUMBER
   IS
   BEGIN
      RETURN LENGTH (self.nm);
   END;
END;

Type created.

Statement 10

With the map method in place, MULTISET operations that require comparisons now work. But wait - only 2 elements in the resulting UNION DISTINCT? But all three rows are distinct - what's going on? Hint: see mapping algorithm.

Now UNION DISTINCT Works

DECLARE
   TYPE limbs_t IS TABLE OF limb_ot;
   l_limbs   limbs_t;
BEGIN
     SELECT limb_ot (l.nm, l.avg_len)
       BULK COLLECT INTO l_limbs
       FROM limbs l
   ORDER BY l.nm;

   l_limbs := l_limbs MULTISET UNION DISTINCT l_limbs;
   DBMS_OUTPUT.put_line ('Lots of limbs! ' || l_limbs.COUNT);
END;

Statement processed.
Lots of limbs! 2

Statement 11

Now MULTISET EXCEPT works - and nothing is left when you "minus" something from itself.

DECLARE
   TYPE limbs_t IS TABLE OF limb_ot;

   l_limbs   limbs_t;
BEGIN
     SELECT limb_ot (l.nm, l.avg_len)
       BULK COLLECT INTO l_limbs
       FROM limbs l
   ORDER BY l.nm;

   l_limbs := l_limbs MULTISET EXCEPT l_limbs;
   DBMS_OUTPUT.put_line ('Lots of limbs! ' || l_limbs.COUNT);
END;

Statement processed.
Lots of limbs! 0

Statement 12

Sorry, there is no way currently in PL/SQL to either create an analogue of a mapping method to a record type or compare records generally.

But Still Not with %ROWTYPE

DECLARE
   TYPE limbs_t IS TABLE OF limbs%ROWTYPE;

   l_limbs   limbs_t;
BEGIN
     SELECT l.nm, l.avg_len
       BULK COLLECT INTO l_limbs
       FROM limbs l
   ORDER BY l.nm;

   l_limbs := l_limbs MULTISET EXCEPT l_limbs;
   DBMS_OUTPUT.put_line ('Lots of limbs! ' || l_limbs.COUNT);
END;

ORA-06550: line 11, column 15:
PLS-00306: wrong number or types of arguments in call to 'MULTISET_EXCEPT_ALL'

More Details: https://docs.oracle.com/error-help/db/ora-06550

Statement 13
Remember that statement that showed only 2 rows were distinct? That's not right - well, wait, but it was correct, given the simplistic mapping algorithm. The names of two limbs were the same: 3, so they mapped to the "same" as for as DISTINCT goes. Let's improve that algorithm!
Tweak Mapping Algorithm
```
CREATE OR REPLACE TYPE BODY limb_ot
IS
   MAP MEMBER FUNCTION limb_map
      RETURN NUMBER
   IS
   BEGIN
      RETURN LENGTH (self.nm) + self.avg_len;
   END;
END;
```
```
Type created.
```

Statement 14

With the new algorithm, the DISTINCT operation now gets a different value for each row and so 3 elements are assigned to l_limbs.

Mapping Algorithms Matter

DECLARE
   TYPE limbs_t IS TABLE OF limb_ot;

   l_limbs   limbs_t;
BEGIN
     SELECT limb_ot (l.nm, l.avg_len)
       BULK COLLECT INTO l_limbs
       FROM limbs l
   ORDER BY l.nm;

   l_limbs := l_limbs MULTISET UNION DISTINCT l_limbs;
   DBMS_OUTPUT.put_line ('Lots of limbs! ' || l_limbs.COUNT);
END;

Statement processed.
Lots of limbs! 3

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database