Problem with running syntactically correct statement and code completion

Discussion of open issues, suggestions and bugs regarding database management and development tools for Oracle
Post Reply
PavelP
Posts: 17
Joined: Sun 03 Sep 2017 10:20

Problem with running syntactically correct statement and code completion

Post by PavelP » 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

alexa

Re: Problem with running syntactically correct statement and code completion

Post by alexa » Fri 14 Dec 2018 10:45

1) If I run the statement below in DBForge (on 18c XE), I'm getting
ORA-00936: missing expression
We will investigate this issue when developing the next product versions.
Another issue I found is adding double-quotes in code completion of %rowtype variables (please see comments in the code below).
This is a new behavior of dbForge. In Oracle, if alias is not enclosed in double quotes, it's supposed to be in upper case and an Oracle server stores it in upper case.

So, if you need the alias to be kept in lower case, you need to enclose it in double quotes.

Code: Select all

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:-).
This is still in our roadmap for the next product versions.

PavelP
Posts: 17
Joined: Sun 03 Sep 2017 10:20

Re: Problem with running syntactically correct statement and code completion

Post by PavelP » Thu 20 Dec 2018 21:25

Hi Alexa,
thanks for your response. Another statement that cannot be executed:
with resp as (
select '{
"CustomerList":{
"items":[
{
"id":4000,
"uid":"DE-P-OEM-IHIOPI-S-G",
"read_only_ad_groups":"DE-P-OEM-IHIOPI-S-G",
"write_ad_groups":"DE-P-OEM-IHIOPI-S-G",
"admin_ad_groups":"DE-P-OEM-IHIOPI-S-G"
},
{
"id":4711,
"uid":"DE-P-OEM-IHIAFA-S-G",
"read_only_ad_groups":"DE-P-OEM-IHIAFA-S-G",
"write_ad_groups":"DE-P-OEM-IHIAFA-S-G",
"admin_ad_groups":"DE-P-OEM-IHIAFA-S-G"
},
{
"id":4712,
"uid":"DE-P-OEM-ADMIN-S-G",
"read_only_ad_groups":"",
"write_ad_groups":"",
"admin_ad_groups":""
}
]
}
}'
r
from dual
) select j.*
from resp,
json_table ( resp.r,'$.CustomerList.items[*]'
columns (
id number(10) path '$.id',
uid_must_be_aliased varchar2 (1000) path '$.uid'
)
)
j;

Interesting thing is that if I select the entire statement and I hit Execute Selection instead of execute current statement, the statement is being executed without any problem. Unfortunately also the formatter fails miserably to format statements like this. Also please try to format the PL/SQL variation of the SQL statement - format does nothing at all.
DECLARE

v_response clob := '{
"CustomerList":{
"items":[
{
"id":4000,
"uid":"DE-P-OEM-IHIOPI-S-G",
"read_only_ad_groups":"DE-P-OEM-IHIOPI-S-G",
"write_ad_groups":"DE-P-OEM-IHIOPI-S-G",
"admin_ad_groups":"DE-P-OEM-IHIOPI-S-G"
},
{
"id":4711,
"uid":"DE-P-OEM-IHIAFA-S-G",
"read_only_ad_groups":"DE-P-OEM-IHIAFA-S-G",
"write_ad_groups":"DE-P-OEM-IHIAFA-S-G",
"admin_ad_groups":"DE-P-OEM-IHIAFA-S-G"
},
{
"id":4712,
"uid":"DE-P-OEM-ADMIN-S-G",
"read_only_ad_groups":"",
"write_ad_groups":"",
"admin_ad_groups":""
}
]
}
}' ;


BEGIN


for r in(
select * from json_table(v_response, '$.CustomerList.items[*]'
COLUMNS
(id VARCHAR2(100) PATH '$.id')) AS id) loop
dbms_output.put_line('id = ' || r.id);

end loop;



END;

Regards,
Pavel

PavelP
Posts: 17
Joined: Sun 03 Sep 2017 10:20

Re: Problem with running syntactically correct statement and code completion

Post by PavelP » Fri 21 Dec 2018 11:35

And another one that cannot be executed (not even as a selected statement):

with function add_one (p_num number) return number is
begin
return p_num + 1;
end;
select 1 empno, add_one(1) empno_plus_1 from dual;

alexa

Re: Problem with running syntactically correct statement and code completion

Post by alexa » Fri 21 Dec 2018 12:45

thanks for your response. Another statement that cannot be executed:
It executes successfully on our side.

Could you please describe it in more detail and provide us a screenshot of any possible error?
also the formatter fails miserably to format statements like this. Also please try to format the PL/SQL variation of the SQL statement - format does nothing at all.
We were able to reproduce this issue and will fix it in one of the next product builds.
And another one that cannot be executed (not even as a selected statement):
with function add_one (p_num number) return number is
...
Unfortunately, we were not able to reproduce this issue.

Could you please let us know in what product you were able to successfully execute this statement and provide us the CREATE definition of the function add_one?

PavelP
Posts: 17
Joined: Sun 03 Sep 2017 10:20

Re: Problem with running syntactically correct statement and code completion

Post by PavelP » Wed 26 Dec 2018 11:40

It executes successfully on our side.
This is interesting, I tested both statements

with resp as (
select '{
"CustomerList":{
"items":[
{
"id":4000,
"uid":"DE-P-OEM-IHIOPI-S-G",
"read_only_ad_groups":"DE-P-OEM-IHIOPI-S-G",
"write_ad_groups":"DE-P-OEM-IHIOPI-S-G",
"admin_ad_groups":"DE-P-OEM-IHIOPI-S-G"
},
{
"id":4711,
"uid":"DE-P-OEM-IHIAFA-S-G",
"read_only_ad_groups":"DE-P-OEM-IHIAFA-S-G",
"write_ad_groups":"DE-P-OEM-IHIAFA-S-G",
"admin_ad_groups":"DE-P-OEM-IHIAFA-S-G"
},
{
"id":4712,
"uid":"DE-P-OEM-ADMIN-S-G",
"read_only_ad_groups":"",
"write_ad_groups":"",
"admin_ad_groups":""
}
]
}
}'
r
from dual
) select j.*
from resp,
json_table ( resp.r,'$.CustomerList.items[*]'
columns (
id number(10) path '$.id',
uid_must_be_aliased varchar2 (1000) path '$.uid'
)
)
j;

on 12c EE and 18c XE, both with the same result (using F8 - execute current statement, NOT F5). If the mouse cursor is at the beginning of the statement (somewhere near the initial with keyword), the result is

1 ORA-00942: table or view does not exist SQL4.sql 35 1


and json_table is underlined with a red curve. If the mouse cursor is placed at the end of the statement (right before the semicolon), I'm getting another error

1 ORA-00900: invalid SQL statement SQL4.sql 38 31


and the number 1000 is underlined. Sorry for not adding a screenshot - it's not directly supported (or I did not find a way how to do so) however I believe you'll be able to reproduce the described behavior with this extended description.
The other one

with function add_one (p_num number) return number is
begin
return p_num + 1;
end;
select 1 empno, add_one(1) empno_plus_1 from dual;

can be executed in SQL Developer and SQL*Plus (well, I must admit that PLSQL Developer fails to execute it as well) provided you're on 12c and higher. There is no external function defined, it uses the inline function defined in the statement (new feature introduced in 12.1 https://docs.oracle.com/database/121/SQ ... m#BABJCBCC also please see the section Examples) and should be possible to execute it as a single SQL statement.

alexa

Re: Problem with running syntactically correct statement and code completion

Post by alexa » Wed 26 Dec 2018 13:43

Thank you for the reply.
1 ORA-00942: table or view does not exist SQL4.sql 35 1 1 ORA-00900: invalid SQL statement SQL4.sql 38 31
We were able to reproduce this issue and will fix it in one of the next product builds.
with function add_one (p_num number) return number is
begin
return p_num + 1;
end;
select 1 empno, add_one(1) empno_plus_1 from dual;
In order to resolve this, you would need to replace ; with / at the end of the script in accordance with https://docs.oracle.com/database/121/SQ ... m#BABJCBCC

PavelP
Posts: 17
Joined: Sun 03 Sep 2017 10:20

Re: Problem with running syntactically correct statement and code completion

Post by PavelP » Thu 27 Dec 2018 12:54

Alexa, thanks for your response, however I must insist that it does not work.
It is possible to execute the command only in this form (without the semicolon), however thanks for your suggestion.

with function add_one (p_num number) return number is
begin
return p_num + 1;
end;
select 1 empno, add_one(1) empno_plus_1 from dual
/

which is not in accordance with the example in Oracle docs

WITH
FUNCTION get_domain(url VARCHAR2) RETURN VARCHAR2 IS
pos BINARY_INTEGER;
len BINARY_INTEGER;
BEGIN
pos := INSTR(url, 'www.');
len := INSTR(SUBSTR(url, pos + 4), '.') - 1;
RETURN SUBSTR(url, pos + 4, len);
END;
SELECT DISTINCT get_domain(catalog_url)
FROM product_information; --see the trailing semicolon
/

moreover it is possible to execute it only using F5 either as an entire script or as a selection, not as a single statement using F8 (or other assigned keyboard shortcut).
It seems that the best thing one can do is to avoid inline functions altogether:-)

alexa

Re: Problem with running syntactically correct statement and code completion

Post by alexa » Fri 28 Dec 2018 12:05

Thank you for letting us know this.

We will take this into account.

VirginiaKnox
Posts: 1
Joined: Wed 13 Feb 2019 08:58
Contact:

Re: Problem with running syntactically correct statement and code completion

Post by VirginiaKnox » Thu 14 Feb 2019 08:24

Need Help! I've being facing ORA-00904: invalid identifier Error in Oracle 11g database multiple times while i am working.


globaltech
Posts: 1
Joined: Mon 02 Nov 2020 22:13
Contact:

Re: Problem with running syntactically correct statement and code completion

Post by globaltech » Mon 02 Nov 2020 22:18

VirginiaKnox wrote: Thu 14 Feb 2019 08:24 Need Help! I've being facing ORA-00904: invalid identifier Error in Oracle 11g database multiple times while i am working.
Did you solve the problem ?

alexa

Re: Problem with running syntactically correct statement and code completion

Post by alexa » Mon 09 Nov 2020 06:56

The error is returned by the server itself. Please see the provided link above describing the issue.

Post Reply