Oracle Sql collection columns in where statement-CodePudding

I want to extract data from tableA based on a column stored inside an apex collection:

 SELECT c001, c002, c003
 FROM apex_collections
 WHERE collection_name = 'CollectionA';

Column c001 contains data that I want to map to another table like:

 SELECT ID, Name, Country
 FROM my_table
 WHERE ID = c001;

How can I combine both tables based on an apex collection column?

CodePudding user response：

Using a join, I guess.

select m.id, m.name, m.country
from my_table m join apex_collections c on c.c001 = m.id
where c.collection_name = 'CollectionA';

If you'll use it in PL/SQL, you'll need an INTO clause (as you have to put selected values into something). If you'll use such a query in a cursor FOR loop, then ... well, it depends on what you'll do next.