Error in compile pl/sql package use in case construct: fetch first 1 rows only

Description

Hello.

I found bug like this:
https://database-navigator.atlassian.net/browse/DBN-305

Information about the plugin:
Version 3.0
Build 5933

In package code dissapears after compile:

Code of my function:

Code dissapears after line:
order by inc.priority;

I think the error is due to clause in "case":
fetch first 1 rows only.

This clause is oracle 12с.

Fix this bug, please.

Environment

None

Activity

Show:
Dmitriy Sidelnikov
September 16, 2016, 11:11 AM
Edited

This problem is reproduced after this code:

merge into ca_violation_fact t
using (
with temp_table as
( select /*+ materialize */
t.measure_point_id
, trunc(t.value_day, 'mm') start_date
, last_day(t.value_day) end_date
from ca_violation_temp t
group by t.measure_point_id
, trunc(t.value_day, 'mm')
, last_day(t.value_day)
)
select t.measure_point_id
, q.start_date
, q.end_date
, max(t.mix_cold_water) m_mix_cold_water
, max(t.over_cons_v) m_over_cons_v
from ca_violation_fact t
, temp_table q
where t.measure_point_id = q.measure_point_id
and t.value_day between q.start_date and q.end_date
group by t.measure_point_id
, q.start_date
, q.end_date
) v
on ( t.measure_point_id = v.measure_point_id
and t.value_day between v.start_date and v.end_date
)
when matched then
update
set t.modifier_id = p_session.id
, t.m_over_cons_v = v.m_over_cons_v
, t.m_mix_cold_water = v.m_mix_cold_water
, t.has_violation =
case
when t.not_exist_necessary_data = pkg_param.c_true
or t.decrease_t = pkg_param.c_true
or t.decrease_v = pkg_param.c_true
or v.m_mix_cold_water = pkg_param.c_true
or t.wrong_conn_device_measure = pkg_param.c_true
or t.negative_value = pkg_param.c_true
or v.m_over_cons_v = pkg_param.c_true
or t.wrong_work_time = pkg_param.c_true
then pkg_param.c_true
else pkg_param.c_false
end;

commit;

– Закрываем шаг загрузки.
pkg_execute.close_stage(l_stage);
exception
when others then
pkg_execute.close_stage
( p_stage => l_stage
, p_status => pkg_param.c_status_error
, p_error => pkg_util.get_error_trace
);

raise_application_error
( -20201
, 'Ошибка в процессе обновления ВД.'||chr(10)||pkg_util.get_error_trace
);
end load_violation;

possible after use CASE clause in MERGE matched update construction.

When troubleshooting, and note it please.

Dmitriy Sidelnikov
September 20, 2016, 8:10 AM

Hello,
when will the patch?

Dmitriy Sidelnikov
October 5, 2016, 6:46 AM

Hello,
please increase the priority issue.

Dan Cioca
October 6, 2016, 9:52 PM

Released with build 6025

Dmitriy Sidelnikov
October 10, 2016, 12:20 PM

Thanks.

Assignee

Dan Cioca

Reporter

Dmitriy Sidelnikov

Labels

YouTrack Ticket

None

YouTrack Reporter

None

Priority

High
Configure