Checking if a collection element exists in Oracle

Question

I create a simple type:

create or replace TYPE SIMPLE_TYPE AS OBJECT (ID NUMBER(38), NAME VARCHAR2(20));

Simple test:

DECLARE
   TYPE ObjectList IS TABLE OF SIMPLE_TYPE;
   tmp SIMPLE_TYPE := SIMPLE_TYPE(1, 'a');
   o ObjectList := new ObjectList(SIMPLE_TYPE(2, 'a'), SIMPLE_TYPE(3, 'a'));
BEGIN
   IF tmp.EXISTS(tmp) THEN
    dbms_output.put_line('OK, exists.');
   END IF;
END;

I get an exception: PLS-00302: component 'EXISTS' must be declared

But this example work:

DECLARE
   TYPE NumList IS TABLE OF INTEGER;
   n NumList := NumList(1,3,5,7);
BEGIN
   n.DELETE(2);
   IF n.EXISTS(1) THEN
      dbms_output.put_line('OK, element #1 exists.');
   END IF;
   IF n.EXISTS(3) = FALSE THEN
      dbms_output.put_line('OK, element #2 has been deleted.');
   END IF;
   IF n.EXISTS(99) = FALSE THEN
      dbms_output.put_line('OK, element #99 does not exist at all.');
   END IF;
END;

Is it possible to implement EXISTS method in SIMPLE_TYPE type?

Answer 1

As the documentation states, EXISTS() tests for the existence of a numbered entry in a collection. That is, array.exists(3) asserts that the third element of array is populated.

What you are trying to do in your first example is test whether the instance tmp matches an element in ObjectList. From 10g onwards we can do this using the MEMBER OF syntax. Unfortunately, in order to make that work we have to declare a MAP method, which is rather clunky and would get rather annoying if the object has a lot of attributes.

SQL> create or replace type simple_type as object
  2       ( id number
  3         , name varchar2(30)
  4         , map member function compare return varchar2);
  5  /

Type created.

SQL>
SQL> create or replace type body simple_type as
  2       map member function compare return varchar2
  3       is
  4          return_value integer;
  5       begin
  6          return to_char(id, '0000000')||name;
  7       end compare;
  8  end;
  9  /

Type body created.

SQL>

Running the example...

SQL> set serveroutput on size unlimited
SQL>
SQL> declare
  2      type objectlist is table of simple_type;
  3      tmp simple_type := simple_type(1, 'a');
  4      o objectlist := new objectlist(simple_type(2, 'a'), simple_type(3, 'a'));
  5  begin
  6      if tmp MEMBER OF o then
  7          dbms_output.put_line('ok, exists.');
  8      else
  9          dbms_output.put_line('search me');
 10      end if;
 11  end;
 12  /
search me

PL/SQL procedure successfully completed.

SQL>

Answer 2

tmp SIMPLE_TYPEE := SIMPLE_TYPE(1, 'a');

…

IF tmp.EXISTS(tmp) THEN

You declare tmp as SIMPLE_TYPE, not ObjectList.

SIMPLE_TYPE is scalar type, not a collection.

Probably you wanted to check o.EXISTS instead (which is an ObjectList)?

Update:

EXISTS when applied to a collection takes an integer index as an argument and checks if the element with this index exists (not its value).

To check that SIMPLE_TYPE(1, 'a') exists in your table, you should so the following:

Create ObjectList in a dictionary:

CREATE TYPE ObjectList IS TABLE OF SIMPLE_TYPE;

Issue the SELECT query:

DECLARE
        tmp SIMPLE_TYPE := SIMPLE_TYPE(1, 'a');
        o ObjectList := new ObjectList(SIMPLE_TYPE(2, 'a'), SIMPLE_TYPE(3, 'a'));
        myid INT;
BEGIN
        SELECT  1
        INTO    myid
        FROM    TABLE(o) q
        WHERE   SIMPLE_TYPE(q.id, q.name) = tmp
                AND rownum = 1;
        IF (myid = 1) THEN
                dbms_output.put_line('OK, exists.');
        END IF;
END;