Tag Archives: ora-00600

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
 
res NUMBER;
 
BEGIN
 
 
   SELECT SUM(num_of_units * cost_of_unit)
   INTO res
   FROM TABLE (i_orders);
 
 
 
   RETURN res;
 
END;

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;
 
CALC_TOTAL(CAST(MULTISET(SELEC
------------------------------
                            36

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;
 
CALC_TOTAL((CAST(MULTISET(SELE
------------------------------
                            36

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

Possible solutions for ORA-00600

If you get the following error:

ORA-00600:Internal error code, arguments: [15610],[],[],[],[],[]
This could be due to Oracle bug 4648181.
This error is usually manifested on Oracle 10g R1 systems (10.1.0.4 and 10.1.0.5) and on earlier version of Oracle 10g R2 (10.2.0.1 and 10.2.0.2) when performing a join.
According to Oracle this is an  Optimizer related bug and it is fixed in Oracle 10.2.0.3 and in 11.1.0.6. patch set  so the best solution would be to upgrade to these version.

This issue was also slated to be fixed in a 10.1.0.6 patch set that was expected mid 2007 but it looks like this fix is delayed. However,  if you have a justified business reason, it is possible to ask Oracle for interim fix.

If any of the options above is not suitable to your needs there are several workaround that you can try:
1) Since this issue is related to the cost based optimizer a simple solution can be to change the query. If you change even a simple thing like the order of the tables in the FROM clause of the query it will force the optimizer to analyze the query differently and may avoid the problem.
of course, this solution can make it perform slower and not always work but it could be a quick and dirty short run solution.
2) Turn off the optimizer cost base transformation by modifying init.ora

set “_optimizer_cost_based_transformation”=off;

This change can be done in the session level as well:

alter session set “_optimizer_cost_based_transformation”=off;

Note: it is usually not recommended to change Oracle underscore _ “hidden settings” but Oracle published this solution in Metalink so I assume it should be OK.

3) Force oracle to use earlier versions optimizer features by modifying init.ora
OPTIMIZER_FEATURES_ENABLE = 10.1.0

This setting will force Oracle to use the optimizer that was used in version 10.1.0 or any other version you provide (rule based).  Any optimizer features that were added after this version will be disabled.