How can I collect into a table of objects, the values produced by a query that has a union in it as shown below
Select customer_name
from customer
where customer_id = 'xxx'
BULK COLLECT INTO customer_obj
UNION
Select customer_name
from customer
where customer_name like '%adam%'
the constraints above are completely made up.
The bulk collect
clause comes right after the (first) select
clause, before the (first) from
clause. You have it in the wrong place.
It is not clear why you are using a union
(although that by itself will not result in an error). Perhaps as an unintended consequence, you will get a list of distinct names, because that is what union
does (as opposed to union all
).
Other than that, as has been pointed out in a Comment already, you don't need union
- you need an or
in the where
clause. But even if you modify your query that way, you still must move bulk collect
to its proper place.