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.

ORA-00920: invalid relational operator

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

ORA-00920: invalid relational operator

Although this error is caused most likely by trivial errors, actually it gives me a chance to talk about some SQL operators whose real meaning and side effects, in my humble opinion, are everything but clearly documented.

Funnily enough, it seems that this error message has been removed altogether from the official documentation starting from version 10R1, but this doesn't mean it cannot be returned!

Let's start then from the error:

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

ORA-00920: invalid relational operator

The problem is in that we cannot perform a comparison in this fashion because on the right hand side of the operator there isn't a single value but a list of values (a list of lists in this case).

However we can easily fix the SQL statement in this way

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

Now, if you have the PL/SQL profiler objects installed in your schema, you should get three rows as result of this query, otherwise you'll get none, but in any case, no syntax errors will be returned.

Note also that the query above is perfectly equivalent to:

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

Personally i tend to write SQL statements preferably using the IN clause rather than = ANY.

Now, instead of using a fixed set of allowed values, let's write a more generic query :

select * from user_objects
where (object_name) = SOME (
select t.table_name
from user_tables t
where t.table_name like 'PLSQL%');

SOME is synonym of ANY, just in case you wonder what's the difference between the two operators.
Again, if the PL/SQL profiler tables are present, at least three rows will be returned.

If we change the operator in this way:

select * from user_objects
where (object_name) = ALL (
select t.table_name
from user_tables t
where t.table_name like 'PLSQL%');

the query will return no rows if the PL/SQL profiler tabs are present, but it will return all user objects if they are not present in your schema.
This means that = ALL behaves like != ALL when the subquery returns no rows!
On the other hand != ALL is perfectly equivalent to NOT IN.

I can't really find = ALL suitable for any purpose off the top of my mind in this moment, i'd say that ALL is most useful when combined with operators like >= or <. For instance, it can be certainly useful to have the possibility of writing queries like:

select * from user_objects
where created > ALL
(select o.created
from user_objects o
where o.object_name like 'PLSQL%');

This query means return all objects newer than the newest object whose name begins with 'PLSQL%'.
By the way, replacing ALL with ANY (or SOME), means finding all objects newer than the oldest object named PLSQL%)

The task of finding out whether the query above is more efficient than the query given below is left to the reader as exercise :-)

select * from user_objects
where created >
(select max(o.created)
from user_objects o
where o.object_name like 'PLSQL%');

Bear in mind however that when the subquery returns no rows (no objects named PLSQL%), the former query will match all user objects, while the latter none and this will certainly have some impact on your application.

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