How to compare two collections values in oracle?

Question

Suppose in one collection(Nested Collection) a I have values (1,2,3,4,5) and in another collection b I have values (5,6,7,8,9). In both collection 5 is common . How to compare and return 5?

Answer 1

With a collection you can use the MULTISET INTERSECT operator:

CREATE TYPE intlist IS TABLE OF int;

DECLARE
  a intlist := intlist(1,2,3,4,5);
  b intlist := intlist(5,6,7,8,9);
  c intlist;
BEGIN
  c := a MULTISET INTERSECT b;

  FOR i in 1 .. c.COUNT LOOP
    DBMS_OUTPUT.PUT( c(i) || ',' );
  END LOOP;
  DBMS_OUTPUT.NEW_LINE();
END;
/

outputs:

5,

I need to compare each value using loop.

Loop through one array and use the MEMBER OF operator to check whether each element is in another array:

DECLARE
  a intlist := intlist(1,2,3,4,5);
  b intlist := intlist(5,6,7,8,9);
  c intlist;
BEGIN
  c := intlist();
  FOR i IN 1 .. a.COUNT LOOP
    IF a(i) MEMBER OF b THEN
      c.EXTEND;
      c(c.COUNT) := a(i);
    END IF;
  END LOOP;

  FOR i IN 1 .. c.COUNT LOOP
    DBMS_OUTPUT.PUT( c(i) || ',' );
  END LOOP;
  DBMS_OUTPUT.NEW_LINE();
END;
/

also outputs:

5,

db<>fiddle