oracle sql varray contains an element

Question

I have a type declaration like this:

  FUNCTION ...
  IS 
     TYPE stati_va IS VARRAY (10000) OF varchar(1);
     stati       stati_va;
     v_counter   INTEGER  := 0;
  BEGIN
      stati := stati_va ();

      --this is actually in a loop so the array contains more values
      v_counter := v_counter + 1;
      stati.EXTEND;
      stati (v_counter) := '4';


      --here I would like to determine if the array 'stati' contains a value

I have tried the following so far but that gives me an errro

      IF '4' member of stati then  <- COMPILE error
      IF '4' IN stati then         <- COMPILE error

I know that looping through the array would be possible but that is a bit inconvinient since I need to build something like this:

     IF array contains '4' then
     elsif array contains '3' then
     elseif array contains '2' then
     ...

Answer 1

You could use the condition:

IF 'element' member OF <my_array> THEN

For example,

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2  TYPE v_array
  3  IS
  4    TABLE OF VARCHAR2(200);
  5    my_array v_array;
  6  BEGIN
  7    my_array := v_array('1','2','3','4');
  8    IF '4' member OF my_array THEN
  9      dbms_output.put_line('yes');
 10    ELSE
 11      dbms_output.put_line('no');
 12    END IF;
 13  END;
 14  /
yes

PL/SQL procedure successfully completed.

SQL>