Many times, when an outside client is calling an oracle function it requires the results in a table format (a collection or an array or data set). The most common way to do so is using Cast and Multiset.
CAST tells Oracle the required datatype for the result of an expression, and MULTISET tells Oracle to put the multiple rows returned by the SELECT statement into a single collection object.
A few days ago I changed a query inside a function using Cast and Multiset. I had a working query with reasonable performance. However, once I compiled the function and tried to use it. The same query that worked great in SQLPlus and PL/SQL developer became painfully slow when using it inside a function.
I checked all the usual suspects like wrong binding of mixed types that could make the optimizer choose a wrong plan and make the query slow. Still, I could not find the reason for the slow performance of the function. After a while, I came to the conclusion that the slow performance is related to the fact that the query is using Union (after all that was the actual change in the function) only then I remembered that using Union together with Multiset will cause your function to simply hang.
one workaround is to use multiset union [all] command.
for example:
create type rec1 as object (rec_id number, text varchar2(20));
create type rec_collect as table of rec1;
SELECT
CAST(MULTISET(
select 1, 'first' from dual
union all
select 2, 'second' from dual
) AS rec_collect)
FROM dual;
ERROR at line 1:
ORA-03113: end-of-file on communication channel
or "Unable to send Break messsage"
Using Multiset Union:
select cast (multiset (select 1, 'first' from dual) as rec_collect)
multiset union -- ALL or distinct
cast (multiset (select 2, 'second' from dual) as rec_collect) rec_collect
from dual
The only problem is that this Multiset Union works only from Oracle 10. In addition it works only with nested table collections and not with varrays.
If your function is pre oracle 10 or you simply need a quick workaround all you need to do is wrap the query with an extra inline view and your function will perform just like the query in SQL Plus.
SELECT
CAST(MULTISET(
select * from
(
select 1, 'first' from dual union all
select 2, 'second' from dual
)
) AS rec_collect)
FROM dual;
UPDATE: This happens with MINUS as well