Find specific varchar in Oracle Nested Table

I'm new to PL-SQL, and struggling to find clear documentation of operations are nested tables. Please correct any misused terminology etc.

I have a nested table type that I use as a parameters for a stored procedure.

CREATE OR REPLACE TYPE "STRARRAY" AS TABLE OF VARCHAR2 (255)

In my stored procedure, the table is initialized and populated. Say I have a VARCHAR2 variable, and I want to know true or false if that varchar exists in the nested table.

I tried

strarray.exists('somevarchar')

but I get an ORA-6502

Is there an easier way to do that other than iterating?

FOR i IN strarray.FIRST..strarray.LAST
    LOOP
        IF strarray(i) = value THEN
            return 1;--found
        END IF;
    END LOOP;

For single value check I prefer the "member" operator.

zep@dev> declare
      2      enames     strarray;
      3      wordToFind varchar2(255) := 'King';
      4  begin
      5      select emp.last_name bulk collect
      6      into   enames
      7      from   employees emp;
      8      if wordToFind member of enames then
      9          dbms_output.put_line('Found King');
     10      end if;
     11  end;
     12  /

Found King

PL/SQL procedure successfully completed

zep@dev> 

You can use the MULTISET INTERSECT operator to determine whether the string you're interested in exists in the collection. For example

declare
  l_enames strarray;
  l_interesting_enames strarray := new strarray( 'KING' );
begin
  select ename
    bulk collect into l_enames
    from emp;
  if( l_interesting_enames = l_interesting_enames MULTISET INTERSECT l_enames )
  then
    dbms_output.put_line( 'Found King' );
  end if;
end;

will print out "Found King" if the string "KING" is an element of the l_enames collection.

You should pass an array index, not an array value to an exists in case you'd like to determine whether this element exists in collection. Nested tables are indexed by integers, so there's no way to reference them by strings.

However, you might want to look at associative arrays instead of collections in case you wish to reference your array element by string index. This will look like this:

DECLARE
  TYPE assocArray IS TABLE OF VARCHAR2(100) INDEX BY VARCHAR2(100);
  myArray assocArray;
BEGIN

  myArray('foo') := 'bar';

  IF myArray.exists('baz') THEN
    dbms_output.put_line(myArray('baz'));

  ELSIF myArray.exists('foo') THEN
    dbms_output.put_line(myArray('foo'));

  END IF;

END;

Basically, if your array values are distinct, you can create paired arrays referencing each other, like, arr('b') := 'a'; arr('a') := 'b';

This technique might help you to easily look up any element and its index.

When a nested table is declared as a schema-level type, as you have done, it can be used in any SQL query as a table. So you can write a simple function like so:

CREATE OR REPLACE FUNCTION exists_in( str VARCHAR2, tab stararray)
  RETURN BOOLEAN
  AS
    c  INTEGER;
  BEGIN
    SELECT COUNT(*)
      INTO c
      FROM TABLE(CAST(tab AS strarray))
      WHERE column_value = str;
    RETURN (c > 0);
  END exists_in;