Oracle PL/SQL/PL SQL/MEMBER OF

MEMBER OF is a logical comparison operator

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

MEMBER OF operator finds if the left operand is a member of the collection used as the right operand

 
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>

MEMBER OF table collection

 
SQL> CREATE OR REPLACE PROCEDURE member_of_example AS
  2    TYPE charTable IS TABLE OF VARCHAR2(10);
  3    emp1 charTable;
  4    result BOOLEAN;
  5  BEGIN
  6    emp1 := charTable("A", "B", "C");
  7    result := "A" MEMBER OF emp1;
  8    IF result THEN
  9      DBMS_OUTPUT.PUT_LINE("""A"" is a member");
 10    END IF;
 11  END member_of_example;
 12  /
Procedure created.