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

Status

Assignee

Dan Cioca

Reporter

Dmitriy Sidelnikov

Labels

YouTrack Ticket

None

YouTrack Reporter

None

Priority

High