Toad World® Forums

Different ORA errors when I copy statement to TOAD which have "tabulation and at least one space before SELECT keyword"

Hi

13.3.0.181

NR1.
I have the following statement. Note that before SELECT keyword I have "\t " (\t - tabulation)
You can have tabulation and multiple spaces after that as well, same problem.
Now this looks perfectly fine statement and I can execute it.

SELECT table_name
FROM dba_tab_statistics
WHERE last_analyzed IS NULL;

Place your mouse cursor at the end, so after semicolon (this error can be intermittent, but if cursor is placed at the end, after the semicolon, then it is reproducible most of the time), and now try to explain plan (ctrl+e).
You'll get:

[Error] Explain Plan (3: 28): ORA-00908: missing NULL keyword

When spooled to screen I get:

explain plan set statement_id='raulk2:081120102451' into SYSTEM.TOAD_PLAN_TABLE For
SELECT table_name
FROM dba_tab_statistics
WHERE last_analyzed IS NUL;

NR2.
Same for example ("\t " before SELECT):

SELECT table_name
FROM dba_tab_statistics
WHERE last_analyzed > sysdate;

When explaining plan, I get

[Error] Explain Plan (3: 26): ORA-00904: "SYSDAT": invalid identifier

And spooled to screen:

explain plan set statement_id='raulk2:081120102605' into SYSTEM.TOAD_PLAN_TABLE For
SELECT table_name
FROM dba_tab_statistics
WHERE last_analyzed > sysdat;

OR

NR3.

SELECT table_name
FROM dba_tab_statistics
WHERE last_analyzed > trunc(sysdate);

I get:

[Error] Explain Plan (3: 36): ORA-00907: missing right parenthesis

And when spooled to screen:

explain plan set statement_id='raulk2:081120102757' into SYSTEM.TOAD_PLAN_TABLE For
SELECT table_name
FROM dba_tab_statistics
WHERE last_analyzed > trunc(sysdate;

OR

NR4.

SELECT table_name
FROM dba_tab_statistics
WHERE last_analyzed > sysdate -1;

[Error] Explain Plan (3: 32): ORA-00936: missing expression

When spooled to screen, this is what I get:

explain plan set statement_id='raulk2:081120102122' into SYSTEM.TOAD_PLAN_TABLE For
SELECT table_name
FROM dba_tab_statistics
WHERE last_analyzed > sysdate -;

Now when I have tabulation and 2 spaces before SELECT keyword "\t ":

SELECT table_name
FROM dba_tab_statistics
WHERE last_analyzed > sysdate;

I get:

[Error] Explain Plan (3: 26): ORA-00904: "SYSDA": invalid identifier

And when spooled to screen, I get:

explain plan set statement_id='raulk2:081120102959' into SYSTEM.TOAD_PLAN_TABLE For
SELECT table_name
FROM dba_tab_statistics
WHERE last_analyzed > sysda;

So looks to me that when I have tabulation and single space, then there will be removed only the last character of that sql statement "sysdate -> sysdat"
When I have tabulation and 2 spaces, then there will be removed 2 characters from the end "sysdate -> sysda"

And so on. WIth 3 spaces probably the third character is removed from the end.

And whenever I remove all spaces before the SELECT keyword, so there will be only tabulation, then it is just fine.

Raul

Noticed that same problem also occurs while executing statements.

Raul

Hello Raul,
This just to let you know that Michael and I are looking into this.
Andre

Noticed that in these examples that I have provided, the tabulation and spaces actually does not show here (seems like code formatting removed these). But nevertheless, you can probably achieve these results by just copying these statements to notepad++, and adding tabulation yourself (+ spaces).
Because if you press "Tab" key in Toad Editor, you get 4 spaces, so this does not work (so Toad editor interprets Tab key with 4 spaces).

But if you copy statement from notepad++ to Toad Editor, then the Tab character stays in place.

Raul

You can easily change tab settings in Toad:

so I'm seeing this:

image

Andre

Ok.
But have you tried, is this problem reproducible in your environment also..?

Raul

Yes, I can reproduce this Raul. Andre and I are looking at it.

Thanks.

So reproducible via explain plan and via execution also, correct..?

Raul

Hi

What is the status of this..`?

Regards
Raul

Ok, I can confirm, it is present in 14.0 also

Raul

Hi Raul. This is still outstanding even in the latest version of the Quest parser which is newer than what Toad 14.0 bundles. I've just spoken with Andre and he's actively improving a few key areas of the parser which will resolve this and other issues. We do not have an ETA at this time.