MEMBER OF operator finds if the left operand is a member of the collection used as the right operand : MEMBER OF « Collections « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Collections
3. MEMBER OF

SQL> CREATE OR REPLACE TYPE list IS TABLE OF NUMBER;
  2  /

Type created.

SQL>
SQL> CREATE OR REPLACE FUNCTION format_list(set_in LIST) RETURN VARCHAR2 IS
  2    returnValue VARCHAR2(2000);
  3  BEGIN
  4
  5      FOR i IN set_in.FIRST..set_in.LAST LOOP
  6         returnValue := set_in(i)||' ';
  7      END LOOP;
  8      RETURN returnValue;
  9  END format_list;
 10  /

Function created.

SQL>
SQL> DECLARE
  2    n VARCHAR2(10) := 'One';
  3    a LIST := list('One','Two','Three');
  4  BEGIN
  5    IF n MEMBER OF a THEN
  6      dbms_output.put_line('"n" is member.');
  7    END IF;
  8  END;
  9  /
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 3


SQL>