Checking if a collection element exists in Oracle
Solution 1
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;
Solution 2
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>
Michał Ziober
Contact me: ziobermichal (at gmail) SO: How does accepting an answer work? What have you tried? If you think, my answers were helpful consider to support me in what I am doing by sending some "satoshi", BTC Address 1Q99xbr6vgKptnFu7RfUkHo37NoiVHawVR
Updated on July 10, 2020Comments
-
Michał Ziober almost 4 years
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?
-
Quassnoi over 14 yearsAre you sure you want
tmp.EXISTS
, noto.EXISTS
?tmp
is scalar even after you corrected its type. It's not a collection.
-
-
WBAR about 11 years
MEMBER OF
.. this is a hint changed my life! AWESOME ! Thanks !