Looking at each value in a VARRAY

You've already done the hard part, isolating one component of each entry in the VARRAY, and you've shown how to use it in a SELECT clause. You can use single elements from the VARRAY in the WHERE clause in exactly the same way:

SELECT DISTINCT
        d.division_name
FROM    division        d
      , TABLE (d.emps)  e
WHERE   e.first_name = 'John';

SELECT  division_name
FROM    division        d
WHERE   EXISTS 
          ( SELECT  'X'   -- Why 'X'?  See note below.
            FROM    TABLE (d.emps)
            WHERE   first_name = 'John'
          );

This problem does not really revolve around the VARRAY: you could use the same techniques on plain old regular vanilla-flavored relational tables:

-- The straightforward but slower solution
SELECT DISTINCT
        d.division_name
FROM    division d
  JOIN  emp      e USING (division_id)
WHERE   e.first_name = 'John';

-- The faster but more convoluted solution
SELECT  division_name
FROM    division d
WHERE   EXISTS
          ( SELECT  'X'
            FROM    emp
            WHERE   first_name = 'John'
              AND   division_id = d.division_id
          );

If you want run the examples, or to experiment with related questions, you might want to copy these statements to populate the tables.

-- After creating the division table, as shown on the main page,
-- the following INSERT statements can be used to populate it.

-- The 'Sales' division will have two employees named John.
INSERT INTO division
(
 division_id,
 division_name,
 emps
)
VALUES
(
 1,
 'Sales',
 emp_va ( emp_obj ('Adams', 'John',    '30-Oct-1735'),
          emp_obj ('Adams', 'Abigail', '11-Nov-1744'),
          emp_obj ('Brown', 'John',    ' 9-May-1800')
        )
);

-- The 'Administration' division will have no employees named John.
INSERT INTO division
(
 division_id,
 division_name,
 emps
)
VALUES
(
 2,
 'Administration',
 emp_va ( emp_obj ('Bingen', 'Hildegard', '17-Sep-1098'),
          emp_obj ('Bush',   'George W.', ' 6-Jul-1946')
        )
);

-- Now the SELECT statements (based on the division table, only)
-- on the main page will run.

CREATE TABLE emp
AS
SELECT  d.division_id
      , e.last_name
      , e.first_name
      , e.birth_dt
FROM    division       d
      , TABLE (d.emps) e;

-- Now the queries on the main page that use the emp table will work.

For More Information