oracle-databaseplsqlunionbulk-collect

BULK COLLECT INTO a UNION query into a table of objects


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.


Solution

  • 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.