We're updating the issue view to help you get more done.
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:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
/** Возвращает список ПС дя УУ (с фильтрацией по ТУ, УСПД, ПУ)
%param p_measure_object_id ID узла учета
%param p_alert_group_code Код группы событий
%param p_object_id ИД объекта событий (ТУ, УСПД, ПУ)
%return Список ПС
*/
function get_incident_object
( p_measure_object_id in measure_object.id%type
, p_alert_group_code in nsi_object_type.code%type := null /*%@$$@%*/
, p_object_id in number := null
)
return sys_refcursor
is
l_moment date := sysdate;
l_result sys_refcursor;
begin
open l_result for
select inc.incident_id
, inc.registered
, inc.measure_object_id
, inc.object_id
, inc.alert_group_code
, inc.problem_id
, inc.problem_code
, inc.problem_name
, pro.description
, inc.priority
, case when inc.problem_code = pkg_problem.c_problem_low_signal then
(select hst.dbm_signal
from device_transfer_hist hst
where hst.device_transfer_id = inc.object_id
and l_moment between hst.start_date and nvl (hst.end_date,l_moment)
order by hst.device_transfer_id desc
fetch first 1 rows only
) -- Низкий уровень сигнала GSM
when inc.problem_code = pkg_problem.c_problem_g1_g2_max then ins.m1 - ins.m2 -- G1 - G2 > max
when inc.problem_code = pkg_problem.c_problem_p1_p2_min then ins.p1 - ins.p2 -- P1 - P2 < min
when inc.problem_code = pkg_problem.c_problem_p1_max then ins.p1 -- P1 > max
when inc.problem_code = pkg_problem.c_problem_t1_max then ins.t1 -- T1 > max
when inc.problem_code = pkg_problem.c_problem_g1_max then ins.m1 -- G1 > max
when inc.problem_code = pkg_problem.c_problem_v1_max then ins.q1 -- V1 > max
when inc.problem_code = pkg_problem.c_problem_p2_max then ins.p2 -- P2 > max
when inc.problem_code = pkg_problem.c_problem_t2_max then ins.t2 -- T2 > max
when inc.problem_code = pkg_problem.c_problem_g2_max then ins.m2 -- G2 > max
when inc.problem_code = pkg_problem.c_problem_v2_max then ins.q2 -- V2 > max
when inc.problem_code = pkg_problem.c_problem_p1_min then ins.p1 -- P1 < min
when inc.problem_code = pkg_problem.c_problem_t1_min then ins.t1 -- T1 < min
when inc.problem_code = pkg_problem.c_problem_g1_min then ins.m1 -- G1 < min
when inc.problem_code = pkg_problem.c_problem_v1_min then ins.q1 -- V1 < min
when inc.problem_code = pkg_problem.c_problem_p2_min then ins.p2 -- P2 < min
when inc.problem_code = pkg_problem.c_problem_t2_min then ins.t2 -- T2 < min
when inc.problem_code = pkg_problem.c_problem_g2_min then ins.m2 -- G2 < min
when inc.problem_code = pkg_problem.c_problem_v2_min then ins.q2 -- V2 < min
end value
from incident_v inc
, measure_instant ins
, nsi_problem_v pro
where inc.measure_object_id = p_measure_object_id
and (p_alert_group_code is null or inc.alert_group_code = p_alert_group_code)
and inc.is_resolved = pkg_param.c_false
and (p_object_id is null or inc.object_id = p_object_id)
and inc.object_id = ins.measure_point_id(+)
and pro.id = inc.problem_id
order by inc.priority;
return l_result;
end get_incident_object;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
