MEMBER

member_condition::=

Description of the illustration member_condition.gif

A member_condition is a membership condition that tests whether an element is a member of a nested table. The return value is TRUE if expr is a member of the specified nested table or varray. The return value is NULL if expr is null or if the nested table is empty.

• expr must be of the same type as the element type of the nested table.

• The OF keyword is optional and does not change the behavior of the condition.

• The NOT keyword reverses the boolean output: Oracle returns FALSE if expr is a member of the specified nested table.

• The element types of the nested table must be comparable. Please refer to "Comparison Conditions " for information on the comparability of nonscalar types.

Example

The following example selects from the table customers_demo those rows in which the cust_address_ntab nested table column contains the values specified in the WHERE clause:

SELECT customer_id, cust_address_ntab
  FROM customers_demo
  WHERE cust_address_typ('8768 N State Rd 37', 47404, 
                         'Bloomington', 'IN', 'US')
  MEMBER OF cust_address_ntab;

CUSTOMER_ID CUST_ADDRESS_NTAB(STREET_ADDRESS, POSTAL_CODE, CITY, STATE_PROVINCE, COUNTRY_ID)
------------ ---------------------------------------------------------------------------------
        103 CUST_ADDRESS_TAB_TYP(CUST_ADDRESS_TYP('8768 N State Rd 37', '47404', 'Bloomington', 'IN', 'US'))

The preceding example requires the table customers_demo and a nested table column containing data. Please refer to "Multiset Operators " to create this table and nested table column.