Checking if a collection element exists in Oracle

exists, oracle, stored-procedures, user-defined-types

Solution

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;

Problem

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?

Original source