Tag Archives: multiset

Run a function with a table of User Defined Type (UDT) as a parameter using SQLPlus

If you want to send multiple records to an oracle function, a common practice is to create the function with one or more parameters as a table of an object type.

In the function itself, you can loop thru the array or select from it using to TABLE command.

Another option to send an array of multiple records is to use XML (xmltype) as the function parameter but this will be addressed in a separate post.

While I use this method for years, for some reason, many times I find myself struggling with the relatively simple syntax. This post will serve me (and maybe some of you) as a short, very simplistic template to copy from.

Basically, what you need to do is the create a user defined object type (UDT)

CREATE OR REPLACE TYPE order_type AS object (
  num_of_units NUMBER
, cost_of_unit NUMBER)

Then you create a table of this user defined type

CREATE OR REPLACE TYPE order_tbl AS  TABLE OF order_type

A simple function

CREATE OR REPLACE FUNCTION calc_total (i_orders order_tbl) RETURN NUMBER IS
   SELECT SUM(num_of_units * cost_of_unit)
   INTO res
   FROM TABLE (i_orders);
   RETURN res;

Testing this function with SQLPlus is a little challenging. The simple way to send a table of UDT type to a function is to cast it to the table of the user defined type.

SQL>   SELECT  calc_total(
  2                      CAST(MULTISET(
  3                        SELECT * FROM
  4                        (
  5                        SELECT 9 num_of_units, 2 cost_of_unit FROM dual UNION ALL
  6                        SELECT 6, 3  FROM dual
  7                        ) ) AS order_tbl   ))
  8    FROM dual;

The reason for the additional inline view is the weird oracle bug the cause the function to hang (and sometimes crash with ora-03113 and ora-00600 ) when using cast and multiset together with union. The inline view is fast workaround for this.
Another option to work around this problem is to use multiset union

SQL>    SELECT  calc_total( (CAST(multiset(
  2                               SELECT 9,2 FROM dual
  3                                MULTISET UNION
  4                               SELECT 6,3 FROM dual
  5                           ) AS order_tbl   ))
  6                       )
  7     FROM dual;

Using Union together with Cast and Multiset can cause your function to simply hang (even if the query works great in SQL Plus)

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 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 * 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