How to use MEMBER OF in PLSQL

Question

I have seen questions with this error, but either are calling external stores or trying with incompatible types or using a varray. So i setup a very simple example and still i can not make it work.

DECLARE 
TYPE mytype IS TABLE OF VARCHAR2(4) INDEX BY PLS_INTEGER;
mytable mytype;
BEGIN
  mytable((mytable.COUNT+1)) := 'COD1';
  mytable((mytable.COUNT+1)) := 'COD2';
  mytable((mytable.COUNT+1)) := 'COD3';
  mytable((mytable.COUNT+1)) := 'COD4';  
  --IF 'COD1' MEMBER OF mytable THEN DBMS_OUTPUT.PUT_LINE('We have the code'); END IF;
  FOR i IN 1..mytable.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(mytable(i));
  END LOOP;
END;

I get this if i run it:

COD1
COD2
COD3
COD4

If i uncomment the IF (what i intend to use) i get this error.

PLS-00306: wrong number or types of arguments in call to MEMBER OF

Perhaps I am not using it correctly or something is wrong.

I am trying to use that on a loop, I save the codes that I have used in the "array" then given one code I need to know if it was already used. My initial solution was to append to a string like ".COD1..COD2." and do a simple INSTR but does not seem right and I like arrays. I heard of that function (member of) which does what I wanted if only work as I believe it should.

can you tell me How to use correctly, what i am doing wrong or how to solve my problem in a better way?

Almost there... I change it to

-- Declare
TYPE mytype IS TABLE OF VARCHAR2(4) INDEX BY VARCHAR(4);
mytable mytype;
-- Fill
mytable('COD1') := 'COD1'; -- kind of redundant I only need the index
-- The magic
IF mytable.EXISTS('COD1')...

But I still feel that there should be a better way.

Answer 1

MEMBER OF can only be used with nested tables. You're trying to use it on an associative array. Here is a nice explanation of the differences.

Answer 2

I have tried to illustrate how you can make use of MEMBER function with NESTED TABLE TYPE. It cannot be used with Asociative arrays. Hope it helps.

SET serveroutput ON;
DECLARE
type my_tab
IS
  TABLE OF VARCHAR2(100);
  tab my_tab;
BEGIN
  tab:=my_tab('AVRAJIT','SHUBHOJIT');
  IF 'AVRAJIT' member OF (tab) THEN
    dbms_output.put_line('Yes');
  ELSE
    dbms_output.put_line('No');
  END IF;
END;