How to use MULTISET UNION DISTINCT on collections

Let’s start with a basic example. Suppose, you have two PL/SQL collections with the same data type that you want to merge and remove the repetitive data. You probably implement your code as follows.

Oracle 19c has been used for the demo.

DECLARE
 
    TYPE REC IS RECORD
    (
        C_ID NUMBER
    );
     
    TYPE T1 IS TABLE OF REC;
 
    ARR1 T1 := T1(REC(1), REC(3), REC(5), REC(7), REC(9));
    ARR2 T1 := T1(REC(1), REC(2), REC(3), REC(4), REC(5));
    ARR3 T1;
 
BEGIN
     
    ARR3 := ARR1 MULTISET UNION DISTINCT ARR2;
     
END;

ORA-06550: satır 16, sütun 13:
PLS-00306: 'MULTISET_UNION_DISTINCT' çağrısındaki bağımsız değişken türlerinin sayısı yanlış
ORA-06550: satır 16, sütun 5:
PL/SQL: Statement ignored

As it is can be seen from the above PL/SQL block, ORA-06550 error is occurred. Even if, the both arrays has the same value and data type. ORA-06550 error is still received.

DECLARE

    TYPE REC IS RECORD
    (
        C_ID NUMBER
    );
    
    TYPE T1 IS TABLE OF REC;

    ARR1 T1 := T1(REC(1));
    ARR2 T1 := T1(REC(1));

BEGIN

    IF ARR1 = ARR2 THEN
        DBMS_OUTPUT.PUT_LINE('EQUAL');
    ELSE
        DBMS_OUTPUT.PUT_LINE('NOT EQUAL');
    END IF;
    
END;

ORA-06550: satır 15, sütun 13:
PLS-00306: '=' çağrısındaki bağımsız değişken türlerinin sayısı yanlış
ORA-06550: satır 15, sütun 5:
PL/SQL: Statement ignored

On the other hand, the same situation does not apply to MULTISET UNION.

Note that SQL set operator UNION and collection set operator MULTISET UNION do not work in the same way. UNION eliminate the repetitional data after merge the data sets whereas MULTISET UNION merge the whole data sets as is.

DECLARE
  
    TYPE REC IS RECORD
    (
        C_ID NUMBER
    );
      
    TYPE T1 IS TABLE OF REC;
  
    ARR1 T1 := T1(REC(1), REC(3), REC(5), REC(7), REC(9));
    ARR2 T1 := T1(REC(1), REC(2), REC(3), REC(4), REC(5));
    ARR3 T1;
  
BEGIN
      
    ARR3 := ARR1 MULTISET UNION ARR2;
    
    FOR I IN 1..ARR3.COUNT
    LOOP
        DBMS_OUTPUT.PUT_LINE('ARR3('||I||') : '||ARR3(I).C_ID);
    END LOOP;
      
END;

ARR3(1) : 1
ARR3(2) : 3
ARR3(3) : 5
ARR3(4) : 7
ARR3(5) : 9
ARR3(6) : 1
ARR3(7) : 2
ARR3(8) : 3
ARR3(9) : 4
ARR3(10) : 5

It works because the data doesn’t need to be compared when MULTISET UNION operator is used. So, what can we do in cases where comparison is necessary, like the MULTISET UNION DISTINCT operator? The MAP function is needed to evaluate comparisons that are implied by the DISTINCT, GROUP BY, UNION, and ORDER BY clauses in common with MULTISET UNION DISTINCT operator. Therefore, we need to create SQL level object which has a MAP member function. Basically, the purpose of MAP function is to help the compiler that how to compare non-scalar types with each other as demonstrated below. After then, we can easily use other multiset operators.

CREATE TYPE TEST_OBJ IS OBJECT(
    C_ID NUMBER,
    MAP MEMBER FUNCTION sort_key RETURN NUMBER
);
CREATE OR REPLACE TYPE BODY TEST_OBJ
IS
    MAP MEMBER FUNCTION sort_key RETURN NUMBER
    IS
    BEGIN
        RETURN C_ID;
    END;
END;
DECLARE
     
    TYPE T1 IS TABLE OF TEST_OBJ;
 
    ARR1 T1 := T1();
    ARR2 T1 := T1();
    ARR3 T1;
 
BEGIN

    
    SELECT 
        TEST_OBJ(C1) BULK COLLECT INTO ARR1 --1, 3, 5, 7, 9
    FROM (
        SELECT 
            CASE WHEN MOD(LEVEL, 2) != 0 THEN LEVEL END C1 
        FROM DUAL CONNECT BY LEVEL < 10
    ) WHERE C1 IS NOT NULL;
    
    SELECT 
        TEST_OBJ(C1) BULK COLLECT INTO ARR2 --1, 2, 3, 4, 5
    FROM (
        SELECT LEVEL C1 FROM DUAL CONNECT BY LEVEL <= 5
    ) WHERE C1 IS NOT NULL;

    ARR3 := ARR1 MULTISET UNION DISTINCT ARR2;
    
    FOR I IN 1..ARR3.COUNT
    LOOP
        DBMS_OUTPUT.PUT_LINE('ARR3('||I||'): '||ARR3(I).C_ID);
    END LOOP;
     
END;

ARR3(1): 1
ARR3(2): 3
ARR3(3): 5
ARR3(4): 7
ARR3(5): 9
ARR3(6): 2
ARR3(7): 4

I hope it helps 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *