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 🙂