Check whether a list is sublist of another list in Oracle PL/SQL

Question

Below is the code snippet of a stored procedure in Oracle 12C. My goal here is to find whether a list (prepared using select statement) is a sublist of another list(pre defined list of 5 items).

TYPE TYPE_LIST IS TABLE OF VARCHAR(200);
CHILD_ITEMS TYPE_LIST;
PARENT_ITEMS TYPE_LIST := TYPE_LIST('item1','item2','item3','item4','item5');

-- collect child items
SELECT A.NAME BULK COLLECT INTO CHILD_ITEMS FROM TABLE_A A, TABLE_B B WHERE A.ID = B.ITEM_ID ;

If CHILD_ITEMS is sublist of PARENT_ITEMS (in this case if all items of CHILD_ITEMS are part of PARENT_ITEMS ('item1','item2','item3','item4','item5')), I have to perform some steps.

if say CHILD_ITEMS contains 'item1','item2' condition should return true. if say CHILD_ITEMS contains 'item1','item2','item100' condition should return false since 'item100' isn't part of PARENT_ITEMS .

So how to write IF condition in this scenario ?

Answer 1

Use the SUBMULTISET operator:

DECLARE
  TYPE TYPE_LIST IS TABLE OF VARCHAR(200);
  CHILD_ITEMS TYPE_LIST := TYPE_LIST('item1', 'item3');
  PARENT_ITEMS TYPE_LIST := TYPE_LIST('item1','item2','item3','item4','item5');
BEGIN
  IF child_items SUBMULTISET OF parent_items THEN
    DBMS_OUTPUT.PUT_LINE( 'Is subset.' );
  ELSE
    DBMS_OUTPUT.PUT_LINE( 'Is not subset.' );
  END IF;
END;
/

Which outputs:

Is subset.

db<>fiddle here