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>

Answer 2

You can't do it with a VARRAY without looping through it.

It is possible to accomplish with associative table:

DECLARE TYPE stati_va IS TABLE OF NUMBER INDEX BY binary_integer; l_array stati_va; BEGIN FOR i IN 1 .. 1000 LOOP l_array(i) := dbms_random.random; END LOOP;

IF (l_array.exists(4))
THEN
    dbms_output.put_line(l_array(4));
END IF;

IF (l_array.exists(234234))
THEN
    dbms_output.put_line('index exists');
ELSE
    dbms_output.put_line('index doesnt' exist');
END IF;

END; /

You can use binary_integer or varchar2 as keys to this array, anything as value

Edit: I have forgotten member syntax. Since it behaves strangely in sql (select statements) we have banned it in our team

it would be (with varray or nested table) if key member of array then ...