Tuesday, January 29, 2008

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.

33 comments:

Anonymous said...

Thx another time, flavio, allways have my oracle error. Tomorrow i'll try to fix it (today is to late :P )

Thx from Spain

Byte64 said...

glad to know it helped, that's why i posted it.

Buenas noches
Flavio

Anonymous said...

Another save over here. Thanks Flavio.

Anonymous said...

Thanks, good post, a real time-saver!

Anonymous said...

It can also happen if you forget a comma between column names or between entries in a decode statement.

Anonymous said...

This error can also occur if you use keywords or functions in your code that an older version of Oracle does not support.
For example, i had code which used CASE statements; it was ported to an Oracle 7 server (...don't ask me why they were still using Oracle 7...), and it produced an ORA-00907 error for each line with a CASE statement.

Byte64 said...

I guess that sometimes companies are just too scared of migrating to a newer version of software or probably they don't want to spend money in this exercise. In the end if they are running the same stuff over and over, who cares to install a newer version if everything is working properly on the old one?

I've seen a lot companies doing the same, it's no surprise, of course we are talking of companies where "state of the art" is a word missing from their vocabulary and software development is not their primary business.

Thanks

Anonymous said...

I have also had this problem when I have embedded Oracle comments ("-- this is a comment") in my query, especially immediately after parenthesis. After tearing my hair out looking for syntax errors, I've removed the comments and had it start working. I don't know the exact circumstances in which this happens, but it's worth a try if you can't think of anything else.

Ed said...

Saved! Named parameters....

Anonymous said...

It also happens if you put an ORDER BY clause in a SELECT UNION inside a subquery, something like this:

SELECT
a, b, c
FROM
(SELECT a, b, c
FROM table_a
ORDER BY a
UNION
SELECT a, b, c
FROM table_b
ORDER BY a
)


Hope this help to someone.

Bye

Giuseppe

Anonymous said...

____Here's another way to get bitten - try having a DECODE without enough parameters. DECODE wants at least four (4) : expression, value1, result1, [...], default. If you only give it three (3) parameters it will eat the closing parenthesis.
Oracle 10gR2XE on SUSE 10

Byte64 said...

anonymous, that's not exact as far as i can see, because decode requires at least three parameters and according to the documentation:

...
DECODE compares expr to each search value one by one. If expr is equal to a search, then Oracle Database returns the corresponding result. If no match is found, then Oracle returns default. If default is omitted, then Oracle returns null....

this behavior is confirmed by the following test:

select decode('a','b',1) v
from dual;

V
------
(null)

I wonder if anything else could go wrong in your specific case.

Flavio

Anonymous said...

Renaming a column in the select statement driving a view will also produce this error.

Byte64 said...

Anonymous,
it would be better to show an example of what you're saying, because when a table column referenced in a view is renamed, different errors can be returned.
For instance if i rename a column mentioned in the selection list, i get ORA-04063, not ORA-00907.

Flavio

Javanian said...

i hit the missing right parenthesis err when i changed my sql ( involves union opr ) from :

Original: works fine.
---------------------
select a.col1, a.col2, a.col3, a.col4, a.col5, a.col8
from TBLA a
where a.col1 =
union
select b.col9, b.col10, b.col11, col12, col13, b.col14
from TBLB b
where b.col9 =
order by 1, 2, 3, 4

to

Modified: Hit ORA-00907
------------------------
select a.col1, a.col2, a.col3, a.col4, a.col5,
(select p.col6 from TBLP p
where p.col5 = a.col7),
a.col8
from TBLA a
where a.col1 =
union
select b.col9, b.col10, b.col11, col12, col13,
NULL, b.col14
from TBLB b
where b.col9 =
order by 1, 2, 3, 4

i merely add new column derived from correlated subquery in select clause. this doesn't seem to meet any of the triggering conditions mention in the post.

Thanks

Byte64 said...

Hi Javanian,
i didn't say there are exactly two situations leading to ORA-00907, i said at least two.

Unfortunately i can't spend much time on this, but a query like this seems to work anyway:

select 1 as a, 2 as b, 3 as c, 4 as d, (select 5 from dual p where 1 = 0) as e, 6 as f
from dual a
where 1 = 1
union
select 1 as a, 2 as b, 3 as c, 4 as d, null as e, 6 as f
from dual b
where 1 = 1
order by 1, 2, 3, 4;

Flavio

Chkiron said...

Flavio,

you're my hero! :-)

Thank you very much!

2Ch

Anonymous said...

Another way to trigger an ORA-00907:

CREATE TABLE FOO (
FOO_NAME VARCHAR2(50) NOT NULL DEFAULT 'Anonymous'
);

(Note that NOT NULL and DEFAULT are swapped.)

Anonymous said...

You get this error for having an order by in a create table...as (CTAS) statement also. Drove me nuts...

przepowiednie said...

Thanx.

I was trying to call a user-defined (PL/SQL) function with named parameters inside a SQL statement and I thought it would be madness to debug it with this error message, but the third website (your webstite) answered it! This was the quick one! ;)

Anonymous said...

Thanks a lot for this help "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."
It saved me at critical time of Production Deployment...

Thanks a ton

Anonymous said...

This message seems to be triggered by an "order by" inside any parentheses, including:

with ...
(select ... order by ...)

Oracle's error messages suck big time. And these limitations on using order by suck too actually.

Kåre M. Kjær said...

There is (at least) one situation where you might want to sort in a nested sql. I used it for an update as
Update table t
set t.value = (select t2.value from othertable t2 where t2.param1 = xyz and t2.param2 = t1.param2 and rownum = 1 order by t2.param3)

Basically the situation I have is that I want to update table t with a value from table t2 but for some records in t, I will have multiple records in t2 that could be used. However, there is a priority in which one of the available values I want to use, depending on param3.

It took me some time to confirm my observation - that the order by was not allowed. In the end, I only had 2 potential values, so I just made 2 versions of the script and ran them in the correct order. But the other had been more elegant.

Teeblog said...

A similar thing is happening to me. We have a case where I need to do something like this:

SELECT F1, F2 from (
(SELECT A F1, B F2 FROM T1 ORDER BY DECODE(INSTR(F1, 'fred'), 1, 0, 1) LIMIT 10)
UNION
(SELECT Y F1, Z F2 FROM T2 ORDER BY DECODE(INSTR(F1, 'fred'), 1, 0, 1) LIMIT 10)
) ORDER BY DECODE(INSTR(F1, 'fred'), 1, 0, 1), F1

There's 2 WHERE clauses in the subselects not shown here that searche for F1 containing 'fred', but we want those that match at the start of the string to be returned first.

This is subject to the same problem with putting ORDER BY in subselects.

I don't understand WHY this quirk exists. Anybody know?

Byte64 said...

Teeblog,
this is not a "quirk", for some reason Oracle raises ORA-00907 because it doesn't know what to with the LIMIT keyword you inserted there.
I imagine that this statement would make sense on some other database system, but not on Oracle.

Flavio

Anonymous said...

hi - I am having a similar issue as to one of your previous postings - I am getting the missing parenthesis out of ODI when I have my lookups be subselects - I need to correct the backend code but want to run the *fix* by you to see if this will work... ODI makes its own staging tables on the fly so recreating is a little complex. It bombs after the select DISTINCT on the first subselect. Can I insert an "AS" before the column name to fix? Is that enough?

insert /*+ append */ into AWP_BI.I$_AWP_SALES_TARGETS_F
(
TARGET_TYPE_KEY,
REGION_KEY,
FAMILY_KEY,
TARGET_DATE,
TARGET,
SEGMENT_KEY,
IND_UPDATE
)
select
TARGET_TYPE_KEY,
REGION_KEY,
FAMILY_KEY,
TARGET_DATE,
TARGET,
SEGMENT_KEY,
IND_UPDATE
from (


select
DISTINCT
(Select AWP_TARGET_TYPES_D.TARGET_TYPE_KEY From AWP_BI.AWP_TARGET_TYPES_D as AWP_TARGET_TYPES_D where UPPER(TRIM(C5_TARGET_TYPE))=UPPER(TRIM(AWP_TARGET_TYPES_D.TARGET_TYPE))) TARGET_TYPE_KEY,
C1_REGION_KEY REGION_KEY,
AWP_PROD_FAMILY_D.FAMILY_KEY FAMILY_KEY,
C2_TARGET_DATE TARGET_DATE,
C3_TARGET TARGET,
(Select AWP_BUSINESS_SEGMENTS_D.SEGMENT_KEY From AWP_BI.AWP_BUSINESS_SEGMENTS_D as AWP_BUSINESS_SEGMENTS_D where UPPER(TRIM(C4_REPORTING_UNIT))=UPPER(TRIM(AWP_BUSINESS_SEGMENTS_D.UNIT_CODE))) SEGMENT_KEY,

'I' IND_UPDATE

from AWP_BI.AWP_PROD_FAMILY_D AWP_PROD_FAMILY_D, AWP_BI.C$_0AWP_SALES_TARGETS_F
where (1=1)
And (UPPER(TRIM(AWP_PROD_FAMILY_D.FAMILY_GROUP))=UPPER(TRIM(C6_PRODUCT_GROUP)))
And (AWP_PROD_FAMILY_D.CURRENT_FLAG = 1)




) S
where NOT EXISTS
( select 1 from AWP_BI.AWP_SALES_TARGETS_F T
where T.TARGET_TYPE_KEY = S.TARGET_TYPE_KEY
and T.REGION_KEY = S.REGION_KEY
and T.FAMILY_KEY = S.FAMILY_KEY
and T.TARGET_DATE = S.TARGET_DATE
and T.SEGMENT_KEY = S.SEGMENT_KEY
and ((T.TARGET = S.TARGET) or (T.TARGET IS NULL and S.TARGET IS NULL))
)

Byte64 said...

I rewrote the statement removing the "AS" after the table names and ORA-00907 goes away, so you may want to try it out:

insert /*+ append */ into AWP_BI.I$_AWP_SALES_TARGETS_F
(
TARGET_TYPE_KEY,
REGION_KEY,
FAMILY_KEY,
TARGET_DATE,
TARGET,
SEGMENT_KEY,
IND_UPDATE
)
select
TARGET_TYPE_KEY,
REGION_KEY,
FAMILY_KEY,
TARGET_DATE,
TARGET,
SEGMENT_KEY,
IND_UPDATE
from (


select
DISTINCT
(Select AWP_TARGET_TYPES_D.TARGET_TYPE_KEY From AWP_BI.AWP_TARGET_TYPES_D AWP_TARGET_TYPES_D where UPPER(TRIM(C5_TARGET_TYPE))=UPPER(TRIM(AWP_TARGET_TYPES_D.TARGET_TYPE))) TARGET_TYPE_KEY,
C1_REGION_KEY REGION_KEY,
AWP_PROD_FAMILY_D.FAMILY_KEY FAMILY_KEY,
C2_TARGET_DATE TARGET_DATE,
C3_TARGET TARGET,
(Select AWP_BUSINESS_SEGMENTS_D.SEGMENT_KEY From AWP_BI.AWP_BUSINESS_SEGMENTS_D AWP_BUSINESS_SEGMENTS_D where UPPER(TRIM(C4_REPORTING_UNIT))=UPPER(TRIM(AWP_BUSINESS_SEGMENTS_D.UNIT_CODE))) SEGMENT_KEY,

'I' IND_UPDATE

from AWP_BI.AWP_PROD_FAMILY_D AWP_PROD_FAMILY_D, AWP_BI.C$_0AWP_SALES_TARGETS_F
where (1=1)
And (UPPER(TRIM(AWP_PROD_FAMILY_D.FAMILY_GROUP))=UPPER(TRIM(C6_PRODUCT_GROUP)))
And (AWP_PROD_FAMILY_D.CURRENT_FLAG = 1)




) S
where NOT EXISTS
( select 1 from AWP_BI.AWP_SALES_TARGETS_F T
where T.TARGET_TYPE_KEY = S.TARGET_TYPE_KEY
and T.REGION_KEY = S.REGION_KEY
and T.FAMILY_KEY = S.FAMILY_KEY
and T.TARGET_DATE = S.TARGET_DATE
and T.SEGMENT_KEY = S.SEGMENT_KEY
and ((T.TARGET = S.TARGET) or (T.TARGET IS NULL and S.TARGET IS NULL))
)

Anonymous said...

I ran into this when creating a BI Publisher data model. I had a requirement to replace zeros with two dashes. Ok cool, decode(value,0,'--',value), done. The SQL ran fine in TOAD, I couldn't for the life of me figure out where I was missing a parenthesis. It took me longer than I care to admit to remember that -- is a comment in Oracle. This works in some places, and it should since it is embedded in quotes, but whatever BI Publisher was using to validate the SQL did not account for this case.

Anonymous said...

I ran into the named parameters problem, but for some reason it doesn't error with a batch execute.

CallableStatement stmt = conn.prepareCall("paramOne => ?, paramTwo => ?");
stmt.setString("paramOne", "foo");
stmt.setString("paramTwo", "bar");

// Standard execute fails
stmt.execute(); // Gives "missing right parenthesis" error

// Batch execute works
stmt.addBatch();
stmt.executeBatch(); // Works fine

unityisplural said...

So, basically this error really means no more than "Syntax error!"

...where the grammar is not-well described by the documentation.

You can also generate the error by leaving out quotes around numeric literals, as in:
select (sysdate - interval 2 day) from dual;

Karen said...

Hi, I am getting the ORA-00907: missing right parenthesis error and the only thing I have added to the previously working SQL is the case statement. Please advise where I am going wrong with the syntax.

select
pv.clientname_singleline, pv.clientaddress_singleline, pv.insuredname, pv.insuredaddress, pv.policynumber, en.admincontactinfo,
en.fullname,
sysdate as mailingdate,
sysdate+34 as cancel_date, pv.agentname, pv.agentnumber, pv.agentaddress,
vs.modelyear||' '||vs.make||' '||vs.model as vehdesc,
vs.vinnumber, tr.effectivedate, bt.billingtypekey, tr.credittotal, tr.debittotal,
(Select tr1.effectivedate from wwr_transaction tr1 where (pv.policyid = tr1.policyid) and (pv.policyvnumber = tr1.policyvnumber) and tr1.description like 'DM%' and rownum<=1) as MissedPaymentDate,
(Select tr1.debittotal from wwr_transaction tr1 where (pv.policyid = tr1.policyid) and (pv.policyvnumber = tr1.policyvnumber) and tr1.description like 'DM%' and rownum<=1) as MissedPayAmnt,
(Select tr2.debittotal from wwr_transaction tr2 where (pv.policyid = tr2.policyid) and (pv.policyvnumber = tr2.policyvnumber) and tr2.description like 'Service%' and rownum<=1) as NSFFeeAmnt,
(Select sum(tr3.debittotal) from wwr_transaction tr3 where (((pv.policyid = tr3.policyid) and (pv.policyvnumber = tr3.policyvnumber) and tr3.effectivedate = tr.effectivedate))) as AmtNextPymnt,
((Select tr1.debittotal from wwr_transaction tr1 where (pv.policyid = tr1.policyid) and (pv.policyvnumber = tr1.policyvnumber) and tr1.description like 'DM%') +
(Select tr2.debittotal from wwr_transaction tr2 where (pv.policyid = tr2.policyid) and (pv.policyvnumber = tr2.policyvnumber) and tr2.description like 'Service%') +
(Select sum(tr3.debittotal) from wwr_transaction tr3 where (((pv.policyid = tr3.policyid) and (pv.policyvnumber = tr3.policyvnumber) and tr3.effectivedate = tr.effectivedate)))) as TOTALOFABOVE,
tr.description,
(case pv.agentnumber
when 2801 then 'abcd'
when 2751 then 'efgh'
else 'ijkl'
end case) as sig
from py_current_policy_v pv
join py_item_v iv on (pv.policyid = iv.policyid) and (pv.policyvnumber = iv.policyvnumber)
left outer join wwp_vehiclesupp vs on (iv.itemid = vs.insitemid) and (iv.itemvnumber = vs.insitemvnumber)
join wwr_transaction tr on (pv.policyid = tr.policyid) and (pv.policyvnumber = tr.policyvnumber)
left outer join wws_billingtype bt on tr.billingtypeid = bt.objectid
full outer join wwx_entity en on en.identity = sig
where pv.policytemplatestatus = 0 and iv.itemtemplatestatus = 0
and bt.billingtypekey = 'S'
and pv.policynumber = '999A01'
and rownum<=1

Byte64 said...

The case statement must end with "end" not "end case".

Please install and use SQL Developer, you'd have found this out immediately.

Regards
Flavio

Anonymous said...

THANK YOU!!!!!!! Order by in a CTAS statement. I didn't even think about it.

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles