I have an array of strings. I want to check if a particular string is present in the array.
DECLARE
TYPE v_array IS TABLE OF VARCHAR2(200);
ais_array v_array;
BEGIN
ais_array := ('Lb1','Lb2','Lb3','Lb613');
IF 'Lb1' IN ais_array THEN
dbms_output.put_line('found');
END IF;
END;
The IN
operator is not working. I tried doing a select *
on the type and then using IN
but that didn't work either.
Any suggestions?
Try member of
condition:
IF 'Lb1' member of ais_array THEN
dbms_output.put_line('found');
END IF;
Oracle introduced several set operators for working with collections in 10g. Please read the documentation to learn more.
MEMBER OF can only be used with nested tables. You're trying to use it on an associative array. You are going to get an error "wrong number or types of arguments in call to 'MEMBER OF' ".
This is how to use it:
DECLARE
TYPE clientele IS TABLE OF VARCHAR2 (64);
client_list_12 clientele := clientele ('Customer 1', 'Customer 2');
client_list_13 clientele := clientele ('Customer 1', 'Customer 3');
client_list_133 clientele
:= clientele ('Customer 1', 'Customer 3', 'Customer 3');
client_list_empty clientele := clientele ();
BEGIN
IF 'Customer 1' MEMBER OF client_list_12
THEN
DBMS_OUTPUT.put_line ('Customer 1 is in the 12 list');
END IF;
IF 'Customer 2' NOT MEMBER OF client_list_13
THEN
DBMS_OUTPUT.put_line ('Customer 2 is not in the 13 list');
END IF;
DBMS_OUTPUT.put_line ('List 133 contains ' || CARDINALITY (client_list_133) || ' items');
IF client_list_empty IS EMPTY
THEN
DBMS_OUTPUT.put_line ('Client list is empty');
END IF;
IF client_list_133 IS NOT EMPTY
THEN
DBMS_OUTPUT.put_line ('Client list 133 is not empty');
END IF;
END;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With