ORA-01797: this operator must be followed by ANY or ALL

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

This is an almost self-explanatory error message that occurs when you write a SQL statement like:

select * from user_objects
where object_name =
('PLSQL_PROFILER_RUNS','PLSQL_PROFILER_UNITS','PLSQL_PROFILER_DATA');

ORA-01797: this operator must be followed by ANY or ALL

I guess it is one of those rarely seen error messages because it requires two conditions:

1. a list of literals (in green color above)
2. a missing comparison operator in the set: ALL, ANY, SOME.
   

I encountered this error while doing some tests for a previous posting regarding ORA-00920, a very similar error, and you may want to refer to that posting for a longer discussion involving these special relational operators.

If the list of literals is replaced by a subquery and if the number of records returned is greater than one, the error returned becomes ORA-01427.

If the query above was meant to return all rows matching any of the three names given in the list, you have two options:

select * from user_objects
where object_name = ANY -- or SOME
('PLSQL_PROFILER_RUNS','PLSQL_PROFILER_UNITS','PLSQL_PROFILER_DATA');

or

select * from user_objects
where object_name IN
('PLSQL_PROFILER_RUNS','PLSQL_PROFILER_UNITS','PLSQL_PROFILER_DATA');

See message translations for ORA-01797 and search additional resources.