Toad World® Forums

The full invalid identifier is not highlighted when getting an Ora-00904 error

When getting an "ORA-00904: "T1"."F4": invalid identifier" message, after running the below SQL, where F4 does not exist, Toad only highlights T1 as the problem SQL. It should highlight T1.F4, since that is what is listed in the error and is really the missing column. Just highlighting T1 leaves the syntax errors fixing for guess work.

select t1.f1, t1.f2, t1.f3, t1.f4 -- I'm asking for field "f4" that does not exists.
from test1 t1;

Submitted this in a service request, but was told it was not a defect and asked to submit it here.

Oracle gives us the position of the error and we highlight it. It's not always what you expect, but it would be a mistake to highlight something other than what Oracle tells us.

After internal discussion, I've learned that Oracle is giving the correct position in this case, and we are starting our highlight there, but the length of the highlight is not what the user expects. This is just open to interpretation because Oracle only gives us the starting position of the query text that is in error, not the length of it. So we highlight to the end of the word. Toad has an option (options -> editor -> behavior -> additional word chars) to control if a dot is considered part of a word or not for the purposes of highlighting. Of course that setting has other effects too (like how much to select when you double-click) so you just have to decide what works best overall for you.

Sorry, but Oracle notifies you about the field and that's how you show it in the error dialog box.
The problem is that it does not being highlighted correctly in the editor.
See the attached print-screen.

Also, if I remember right, this problem did not exists at older version of TOAD like version 11.6 when running on the same DB, with the same version of Oracle.

Well, sort of.
When an error occurs, Oracle gives us:

  1. The ORA- error number
  2. Some text to go with it (which could be in any language)
  3. The starting position of the error.

Toad shows the error number and text. Toad uses the error position to highlight the word at the position of the error in your SQL. I don't think it's reasonable to expect Toad to parse the error message text (which could be in any language, and is different for every error message) for the purpose of determining what to highlight in the SQL..

I have been working with Support and QA on this request and 11.0, 11.6, and 12.0 all function the same as in current version.