EDIT command misbehaving

Toad for Oracle
1

This was quite a scary Monday morning...

edit member where mbr_id = 300457

→ no rows returned (incorrect)

select rowid, member.* from member where mbr_id = 300457

→ 1 row returned (correct)

next attempt:

edit member where mbr_id >= 300457

→ 500+ rows returned, among which... uhm... MBR_ID 238495, 237283, ... ?!?

select rowid, member.* from member where mbr_id >= 300457

→ 12 rows returned (correct)

  • MEMBER.MBR_ID is a NUMBER (8)
  • TOAD 16.2.98
  • same behavior with both direct and Oracle 19 Client connections
2

Thank you for reporting this.

As you probably know, EDIT is not a real SQL command, so Toad changes it to real SQL behind the scenes.

There was a change in 16.2 that allows EDIT command to be multi-line (so WHERE can be on the next line, etc). We rely on the parser to do this. But in your example, MEMBER is a reserved word, and the parser doesn't know what to do with it. If you run it with F9, you get a SQL error. If you run it with CTRL+Enter, the first digit of the literal at the end is getting cut off. You can see this with spool sql.

This was fixed for version 16.3.

3

Super, thx for the quick and most useful reply!

Now I not only understand what happened and know it will be fixed in the next release, but I also learned how I could have investigated myself with the spool sql feature (which I don't use enough) :+1:

1 Like