A SELECT statement using IN with predicate surrounded by parens and ended with semicolon gives the following error:
ORA-00920: invalid relational operator
Removing the semicolon (or parens if possible) then allows the query to run and complete successfully.
Examples:
select * from all_tables
where (owner, table_name) in (
select 'SYS', 'DUAL' from dual
union select 'SYS', 'PLAN_TABLE$' from dual
);
select * from all_tables
where (owner) in (
select 'SYS' from dual
union select 'SYSTEM' from dual
);
Taking the last example, if you remove the parens surrounding "owner" if will work successfully. In either example, removing the trailing semicolon will allow the query to execute.
It happens every time I attempt to execute the query without exiting Toad. I tried exiting Toad a couple times and then restart it. Each time it still produced the error.
This error happens only error only when placing the cursor at the end of the statement and pressing Ctrl+Enter or shift+F9.
Starting in Toad for Oracle 11, it is advised not use this workflow anymore.
Either use F9 when the cursor is at the end of the statement or Ctrl+Enter (or shift+F9) if the whole statement is highlighted.
These actions and settings were changed somewhat from old behavior for a variety of reasons (Editor modifications, user requests, etc.).
