BULK COLLECT INTO a UNION query into a table of objects

Question

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.

Answer 1

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.

Answer 2

Another option would be to put your UNION into an inline view. For example,

SELECT          cust.customer_name 
BULK            COLLECT 
INTO            customer_obj
FROM (
  SELECT        customer_name
  FROM          customer
  WHERE         customer_id = 'xxx'
  UNION
  SELECT        customer_name
  FROM          customer
  WHERE         customer_name LIKE '%adam%'
) cust