Oracle PL/SQL Tutorial/Collections/MEMBER OF

MEMBER OF is a logical comparison operator

   <source lang="sql">

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</source>

MEMBER OF Operator

MEMBER OF operator checks whether an element is in a nested table.

   <source lang="sql">

SQL> CREATE OR REPLACE PROCEDURE member_of_example AS

 2    TYPE nestedTableType IS TABLE OF VARCHAR2(10);
 3    myTable1 nestedTableType;
 4    result BOOLEAN;
 5  BEGIN
 6    myTable1 := nestedTableType("F", "G", "S");
 7    result := "George" MEMBER OF myTable1;
 8    IF result THEN
 9      DBMS_OUTPUT.PUT_LINE("""George"" is a member");
10    END IF;
11  END member_of_example;
12  /

Procedure created. SQL> CALL member_of_example(); Call completed. SQL> SQL></source>

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

   <source lang="sql">

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></source>

MEMBER OF table collection

   <source lang="sql">

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.</source>