The Smart Way to Check if an Element Exists in a Collection

When you need to find out if a specific element exists in a collection or not, how do you go about it? Do you loop through the list till you find the value?

If that’s what you do, this post is for you.

You can check for a specific element in a collection without checking each value one by one in iteration. 10G onwards, Oracle provides a simpler, more efficient way to do it.

A test case to demonstrate how:

In an application, some tables support soft delete, some do not. Soft delete is implemented using a column named IS_DELETED. So if a table has this column, it can be said that the table supports soft delete.

You want to write a function is_soft_delete_enabled, which:

• takes as input the table name,
• reads the table columns from user_tab_cols,
• looks for IS_DELETED in the column list, and returns TRUE is found, else FALSE

Check Element Existence using a Loop

Here’s an old-fashioned, pre-10G way to find the existence of an element in a nested table.

SQL> CREATE OR REPLACE FUNCTION
  2    is_soft_delete_enabled (p_tab VARCHAR2)
  3  RETURN BOOLEAN
  4  IS
  5
  6    TYPE column_typ
  7      IS TABLE OF
  8      user_tab_cols.column_name%TYPE;
  9
 10    l_column_list   column_typ;
 11
 12  BEGIN
 13
 14    -- Get the list of elements into
 15    -- nested table variable l_column_list
 16    EXECUTE IMMEDIATE
 17      'SELECT column_name
 18       FROM user_tab_cols
 19       WHERE table_name= '''||p_tab||''''
 20    BULK COLLECT INTO l_column_list;
 21
 22    -- Pre-10G implementation: loop
 23    -- through the list of elements till
 24    -- the required element is found
 25    FOR i IN 1..l_column_list.COUNT LOOP
 26      IF (l_column_list(i) = 'IS_DELETED') THEN
 27        RETURN TRUE;
 28      END IF;
 29
 30    END LOOP;
 31
 32    RETURN FALSE;
 33
 34  END;
 35  /

Function created.

When executed:

SQL> BEGIN
  2
  3    IF (is_soft_delete_enabled('TEST')) THEN
  4      dbms_output.put_line
  5        ('Soft Delete enabled');
  6    ELSE
  7      dbms_output.put_line
  8        ('Soft Delete not enabled');
  9    END IF;
 10
 11  END;
 12  /
Soft Delete enabled

PL/SQL procedure successfully completed.

Check Element Existence without using a Loop

10G onwards, the set operator MEMBER OF can be applied to test if an element is part of a set or not.

SQL> CREATE OR REPLACE FUNCTION
  2    is_soft_delete_enabled (p_tab VARCHAR2)
  3  RETURN BOOLEAN
  4  IS
  5
  6    TYPE column_typ
  7      IS TABLE OF
  8      user_tab_cols.column_name%TYPE;
  9
 10    l_column_list   column_typ;
 11
 12  BEGIN
 13
 14    -- Get the list of elements into
 15    -- nested table variable l_column_list
 16    EXECUTE IMMEDIATE
 17      'SELECT column_name
 18       FROM user_tab_cols
 19       WHERE table_name= '''||p_tab||''''
 20    BULK COLLECT INTO l_column_list;
 21
 22    -- The smart way: use MEMBER OF
 23    -- to locate the element in the
 24    -- nested table; need not loop
 25
 26      IF 'IS_DELETED' MEMBER OF
 27        l_column_list THEN
 28        RETURN TRUE;
 29      END IF;
 30
 31
 32    RETURN FALSE;
 33
 34  END;
 35  /

Function created.

When executed:

SQL> BEGIN
  2
  3    IF (is_soft_delete_enabled('TEST')) THEN
  4      dbms_output.put_line
  5        ('Soft Delete enabled');
  6    ELSE
  7      dbms_output.put_line
  8        ('Soft Delete not enabled');
  9    END IF;
 10
 11  END;
 12  /
Soft Delete enabled

PL/SQL procedure successfully completed.

For further reading:

• StackOverflow: Fastest Way in PL/SQL to See if Value Exists: List, VARRAY, or Temp Table
• Collection Methods