Имеем Oracle Database 10g Express Edition Release 10.2.0.1.0, в котором запрос отрабатывает на ура.
А вот в Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 выдает подряд 2 ошибки: ORA-03113 и ORA-03114.
Схема запроса.
with table (
select
expence
, tarif_value0
, tarif_name0
, tarif_value1
, tarif_name1
, pkg.with_connect_func(t2.id, sysdate) bad_field
from t0
join t1 on ...
join t2 on ...
join t3 on ...
join t4 on ...
join t5 on ...
join t6 on ...
join t7 on ...
join t8 on ...
left outer join t8 t9 on ...
union all
select
expence
, tarif_value0
, tarif_name0
, tarif_value1
, tarif_name1
, pkg.with_connect_func(t2.id, sysdate) bad_field
from t0
join t1 on ...
join t2 on ...
join t3 on ...
join t4 on ...
join t5 on ...
join t6 on ...
join t7 on ...
join t8 on ...
left outer join t8 t9 on ...
union all
select
expence
, tarif_value0
, tarif_name0
, tarif_value1
, tarif_name1
, pkg.with_connect_func(t0.id, sysdate) bad_field
from t0 t01
join t1 t11 on ...
join t2 t21 on ...
join t3 t31 on ...
join t3 on ...
join t2 on ...
join t4 on ...
join t5 on ...
join t6 on ...
join t7 on ...
) select
sum(expence * tarif_value0)
, tarif_name0
, bad_field
from table
group by tarif_name0
, bad_field
union all
select
sum(expence * tarif_value1)
, tarif_name1
, bad_field
from table
group by tarif_name1
, bad_field
Функция pkg.with_connect_func() содержит всего 1 запрос и возвращает его результат:
select * into result from (
select t0.id
from t3
join t2 on ...
join t1 on ...
join t0 on ...
start with t2.id = :paramter -- это параметер, передаваемый в функцию
connect by prior decode(t3.t2_id, t2.id, null, 0, decode(t0.field, 70, NULL, t3.t2_id), t3.t2_id) = t2.id
order by level desc
) where rownum < 2;
Ошибка устраняется, если закомментировать в запросе вызов этой самой функции. В логах Oracle, написано
ORA-07445: exception encountered: core dump [ACCESS_VIOLATION] [_opiptp+50] [PC:0x158546E] [ADDR:0xA82737C] [UNABLE_TO_READ] []
Current SQL statement for this session:
<запрос из функции>
Как все это дело можно заставить работать?