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.

score 6 · Answer 1 · answered Feb 02 '17 at 05:32

6

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;

answered Feb 02 '17 at 05:32

Avrajit Roy

3,233
1
13
25

It works even without round brackets: IF 'AVRAJIT' member OF tab THEN – Splash Mar 24 '20 at 09:53

score 4 · Answer 2 · answered Feb 01 '17 at 20:22

4

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.

answered Feb 01 '17 at 20:22

DCookie

42,630
11
83
92

Oh I see thanks, do you happen to know how can i find an element in an assosiative array, for the life of me, I tried googling everything without success. – Neto Yo Feb 01 '17 at 23:51
You've done it in your edit above. Use mytable.EXISTS. Perhaps you should define what you mean by "better way". – DCookie Feb 02 '17 at 14:34
I mean in the way I understand arrays are this way array[index] = value, so a "better way" would be to find the "value" not the "index" which purpose should not be storing the value itself. I might be wrong and if it works... – Neto Yo Feb 02 '17 at 20:44
1

You could assign anything for the value, e.g., mytable('COD1') := 1; and the EXISTS test will still work. – DCookie Feb 02 '17 at 22:49

How to use MEMBER OF in PLSQL

2 Answers2