ORA-00907: missing right parenthesis

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

There are at least a couple of situations where you may come across this syntax error message (see update at the bottom):

1. a trivial mistake, something that you could easily avoid by using SQLDeveloper's editor, that comes with a cool matching parentheses visual checking feature;
2. as a result of an elusive forbidden syntax form that is not clearly documented in the official books (through version 11.1 at time of writing).

Let's forget the former case and go straight to the latter case.

Don't get my example wrong, i know this is not the best way of doing this, but I'll talk about that later on:

select object_name, object_type
from user_objects
where object_type in (
select column_value
from table(csv_to_table('SYNONYM,PROCEDURE,FUNCTION,VIEW,TABLE'))
order by 1);

ORA-00907: missing right parenthesis

Clearly when one gets a message like this, the first reaction is probably to verify what parenthesis has been left out, but unfortunately there are no missing parentheses at all in this statement.

To cut it short, the untold syntax quirk is summarized as follows: don't use ORDER BY inside an IN subquery.

Now, one may object that indeed it doesn't make sense to use the ORDER BY inside an IN clause, which is true, because Oracle doesn't care about the row order inside an IN clause:

select object_name, object_type
from user_objects
where object_type in ('SYNONYM,PROCEDURE,FUNCTION,VIEW,TABLE');

is perfectly equivalent to:

select object_name, object_type
from user_objects
where object_type in ('FUNCTION,TABLE,SYNONYM,PROCEDURE,VIEW');

Oracle may or may not process the rows in the same order, probably it will depend on which blocks it finds in the buffer cache, which can vary between the first execution and the second execution, so don't rely on swapping items in the IN clause if you want to change the order in which they are processed.

Let's go back to the original query: in the example above i used the custom function csv_to_table to convey the idea of a query with a parametric IN clause, that is a clause that is not made up of literal values but that could accept a string parameter (a comma separated string list) that could be set somewhere else.

So, if the purpose of this query was to process the rows in the USER_OBJECTS view in the specified object type order, then we would have to rewrite the query completely:

select a.object_name, a.object_type
from user_objects a, (
select column_value object_type, rownum as n
from table(csv_to_table('SYNONYM,PROCEDURE,FUNCTION,VIEW,TABLE'))
order by n) b
where a.object_type = b.object_type
order by b.n;

Note also that there are at least two ways of solving the syntax problem without touching the ORDER BY clause:

creating a view as follows:

create or replace my_object_types_v as
select column_value as object_type
from table(csv_to_table('SYNONYM,PROCEDURE,FUNCTION,VIEW,TABLE'))
order by 1;

and then issue:

SELECT object_name, object_type
from user_objects
where object_type in (select object_type from my_object_types_v);

or alternatively use the WITH clause:

WITH my_object_types_v as (select column_value as object_type
from table(csv_to_table('SYNONYM,PROCEDURE,FUNCTION,VIEW,TABLE'))
order by 1)
SELECT object_name, object_type
from user_objects
where object_type in (select object_type from my_object_types_v);

but as already remarked, neither of the two will force Oracle to process the rows of USER_OBJECTS in the given order.

Updated february 29, 2008:
This error is also returned when calling a user-defined (PL/SQL) function with named parameters inside a SQL statement:

SELECT my_function(p_input_value => 0) AS my_fn
FROM DUAL;

ORA-00907: missing right parenthesis

Named parameters are only allowed in PL/SQL programs (but not in SQL statements inside PL/SQL programs), therefore the solution is to pass parameters in positional form.

Read more about the different parameter passing options in the Oracle 10G documentation.

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