Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE OR REPLACE TYPE class_t IS VARRAY(9) OF VARCHAR(5);
create table experiment(name VARCHAR2(20), seat class_t);
BEGIN
insert into experiment(name,seat) values('JPZ',class_t('AC1','EC'));
insert into experiment(name,seat) values('ABC',class_t('GEN','EC'));
insert into experiment(name,seat) values('DFG',class_t('AC1','EC'));
END;
/
1 rows affected

-- Does not work for a VARRAY
-- Would work for a nested table collection.
SELECT *
FROM   experiment
WHERE  'AC1' MEMBER OF seat;
ORA-00932: inconsistent datatypes: expected UDT got FIDDLE_CSLSPTMSZPVLURNKEZLT.CLASS_T

SELECT name
FROM   experiment e
WHERE  EXISTS (SELECT 1 FROM TABLE(e.seat) WHERE COLUMN_VALUE = 'AC1');


NAME


JPZ

DFG


SELECT name
FROM   experiment e
       CROSS APPLY TABLE (e.seat) s
WHERE  s.COLUMN_VALUE = 'AC1';


NAME


JPZ

DFG


SELECT name, column_value
FROM   experiment e
       CROSS APPLY TABLE (e.seat) s;


NAME
COLUMN_VALUE


JPZ
AC1

JPZ
EC

ABC
GEN

ABC
EC

DFG
AC1

DFG
EC