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
October 10, 2016, 12:20 PM

Thanks.

Dan Cioca
October 6, 2016, 9:52 PM

Released with build 6025

Dmitriy Sidelnikov
October 5, 2016, 6:46 AM

Hello,
please increase the priority issue.

Dmitriy Sidelnikov
September 20, 2016, 8:10 AM

Hello,
when will the patch?

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.

Fixed

Assignee

Dan Cioca

Reporter

Dmitriy Sidelnikov

Labels

YouTrack Ticket

None

YouTrack Reporter

None

Priority

High