Oracle 'MEMBER OF' function

Question

I was trying to use MEMBER OF function but receiving error "PLS-00306: wrong number or types of arguments in call to 'MEMBER OF'.". Please let me know, how i can use 'MEMBER OF' function correctly, i want to avoid iteration.

  Table: Employee
    EMP_ID  EMP_NAME    EMP_BRANCH
     1       Oliver       104
     2       Harry        105
     3       Jack         105
     4       Jacob        103
   
DECLARE
TYPE emp_name_type IS RECORD(emp_name employee.emp_name%type);
TYPE emp_name_table_type IS TABLE OF emp_name_type;
emp_name_table_type_array emp_name_table_type;
BEGIN
 select x.emp_name BULK COLLECT
 into emp_name_table_type_array
 from (
       select e.emp_name, min(e.emp_id) as emp_id
       from employee e
       where e.emp_branch = 105
       group by e.emp_name
      ) x
       order  by x.emp_id;

 -- receiving output Harry + Jack
 FOR i IN emp_name_table_type_array.FIRST .. emp_name_table_type_array.LAST LOOP
     dbms_output.put_line(emp_name_table_type_array(i).emp_name);
 END LOOP; 

 -- here i want to implement something like bellow using 'Member OF' function
 IF 'Harry' MEMBER OF emp_name_table_type_array THEN
   dbms_output.put_line('yes');
 ELSE
   dbms_output.put_line('no');
 END IF; 
END;
/

Thank you

Answer 1

You have one type declaration too many (and have to fix the first dbms_output.put_line call because of that).

SQL> DECLARE
  2    TYPE emp_name_table_type IS TABLE OF employee.emp_name%type;
  3    emp_name_table_type_array emp_name_table_type;
  4  BEGIN
  5    select x.emp_name BULK COLLECT
  6    into emp_name_table_type_array
  7    from (
  8          select e.emp_name, min(e.emp_id) as emp_id
  9          from employee e
 10          where e.emp_branch = 105
 11          group by e.emp_name
 12         ) x
 13         order  by x.emp_id;
 14
 15    -- receiving output Harry + Jack
 16    FOR i IN emp_name_table_type_array.FIRST .. emp_name_table_type_array.LAST LOOP
 17       dbms_output.put_line(emp_name_table_type_array(i));
 18    END LOOP;
 19
 20    -- here i want to implement something like bellow using 'Member OF' function
 21    IF 'Harry' MEMBER OF emp_name_table_type_array THEN
 22       dbms_output.put_line('yes');
 23    ELSE
 24       dbms_output.put_line('no');
 25    END IF;
 26  END;
 27  /
Harry
Jack
yes

PL/SQL procedure successfully completed.

SQL>