Find specific varchar in Oracle Nested Table

Question

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;

Answer 1

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> 

Answer 2

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.