how to retrive data from VARRAY and filter it

Question

i have to retrieve specific data from Varray for example

CREATE OR REPLACE TYPE class_t IS VARRAY(9) OF VARCHAR(5);/   
create table exprement(name VARCHAR2(20), seat class_t);
insert into exprement(name,seat) values('JPZ',class_t('AC1','EC'));
insert into exprement(name,seat) values('ABC',class_t('GEN','EC'));
insert into exprement(name,seat) values('DFG',class_t('AC1','EC'));

i want to retrieve the name where VARRAY includes AC1 i tried select * from exprement where seat='AC1';

Answer 1

You can use:

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

or

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

---

As an aside, if you defined seat as a nested table (rather than a VARRAY) then you could use the MEMBER OF operator:

SELECT *
FROM   experiment
WHERE  'AC1' MEMBER OF seat;

But that doesn't work for VARRAYs or associative arrays.

db<>fiddle here