Problem with running syntactically correct statement and code completion
Posted: Tue 11 Dec 2018 13:45
Hi,
I would like to report two issues.
1) If I run the statement below in DBForge (on 18c XE), I'm getting
ORA-00936: missing expression
error, if the mouse cursor is somewhere in the middle of the statement. If I place the mouse cursor right before the terminating semicolon, the output is
xecute succeeded. 0 rows affected [< 0,001s]
however no rows are selected.
In other IDEs (and sqlplus as well) the very same statement can be executed with no problems.
---------------------------------------------------------------------------------------------
with machine_data(machine,data) as(--intentionally unsorted
select 'M4',810 from dual union
select 'M1', 400 from dual union
select 'M5', 900 from dual union
select 'M2',520 from dual union
select 'M3',615 from dual union
select 'M6',1100 from dual ),
sorted_data as( --get the data sorted with row number
select rownum recno, m.* from machine_data m), min_dist_rec as(
select min(recno) recno from sorted_data where data = (select data from sorted_data where data < 624 order by data desc fetch first row only)) --get the row with the slightest difference
select s.recno,
s.machine,
s.data,
case s.recno
when r.recno then
round(( lead(s.data,1) over (order by s.recno) - 624 ) / (lead(s.data,1) over (order by s.recno) - data) * 100) || '%'
else null
end next_data
from sorted_data s,min_dist_rec r where s.recno in(r.recno - 1,r.recno,r.recno+1) order by s.recno;
---------------------------------------------------------------------------------------------
RECNO MA DATA NEXT_DATA
---------- -- ---------- -----------------------------------------
2 M2 520
3 M3 615 95%
4 M4 810
I have sometimes problems with syntactically correct commands execution but finally this time I was able to create a reproducible showcase .
Another issue I found is adding double-quotes in code completion of %rowtype variables (please see comments in the code below).
---------------------------------------------------------------------------------------------
declare
dept_t dept%rowtype;
begin
select * into dept_t from dept where deptno = 10;
--after typing dbms_output.put_line(dept_t. the code suggestion pops up
--but after choosing deptno from the list, i'm getting deptno enclosed in double-quotes as shown below instead of dept_t.deptno
dbms_output.put_line(dept_t."deptno");--double-quotes should not be here
end;
---------------------------------------------------------------------------------------------
And please (please please please), could you review once more this
viewtopic.php?f=22&t=36025
feature request? The code editor is almost perfect and this feature (available in all other major IDEs) would bring it to another level:-).
Regards,
Pavel
I would like to report two issues.
1) If I run the statement below in DBForge (on 18c XE), I'm getting
ORA-00936: missing expression
error, if the mouse cursor is somewhere in the middle of the statement. If I place the mouse cursor right before the terminating semicolon, the output is
xecute succeeded. 0 rows affected [< 0,001s]
however no rows are selected.
In other IDEs (and sqlplus as well) the very same statement can be executed with no problems.
---------------------------------------------------------------------------------------------
with machine_data(machine,data) as(--intentionally unsorted
select 'M4',810 from dual union
select 'M1', 400 from dual union
select 'M5', 900 from dual union
select 'M2',520 from dual union
select 'M3',615 from dual union
select 'M6',1100 from dual ),
sorted_data as( --get the data sorted with row number
select rownum recno, m.* from machine_data m), min_dist_rec as(
select min(recno) recno from sorted_data where data = (select data from sorted_data where data < 624 order by data desc fetch first row only)) --get the row with the slightest difference
select s.recno,
s.machine,
s.data,
case s.recno
when r.recno then
round(( lead(s.data,1) over (order by s.recno) - 624 ) / (lead(s.data,1) over (order by s.recno) - data) * 100) || '%'
else null
end next_data
from sorted_data s,min_dist_rec r where s.recno in(r.recno - 1,r.recno,r.recno+1) order by s.recno;
---------------------------------------------------------------------------------------------
RECNO MA DATA NEXT_DATA
---------- -- ---------- -----------------------------------------
2 M2 520
3 M3 615 95%
4 M4 810
I have sometimes problems with syntactically correct commands execution but finally this time I was able to create a reproducible showcase .
Another issue I found is adding double-quotes in code completion of %rowtype variables (please see comments in the code below).
---------------------------------------------------------------------------------------------
declare
dept_t dept%rowtype;
begin
select * into dept_t from dept where deptno = 10;
--after typing dbms_output.put_line(dept_t. the code suggestion pops up
--but after choosing deptno from the list, i'm getting deptno enclosed in double-quotes as shown below instead of dept_t.deptno
dbms_output.put_line(dept_t."deptno");--double-quotes should not be here
end;
---------------------------------------------------------------------------------------------
And please (please please please), could you review once more this
viewtopic.php?f=22&t=36025
feature request? The code editor is almost perfect and this feature (available in all other major IDEs) would bring it to another level:-).
Regards,
Pavel