ORA-00907 MISSING RIGHT PARENTHESIS


OK this is oracle sql error. the first step to trouble shoot it is by spotting what the error said: looks for missing right parenthesis.
But unfortunately, this is not the only case for this error thrown out. Even when we match the count of left and right parenthesis, error ORA-00907 still can be thrown out. some of the cases are:
1. In program, for multi lines sql query, if you put comment it will throw error. ex.
sql = ” select a,round(b,2 –comment ”
sql &= ” ) from table ”

This happened because multi lines readed as a long line, so the above example is read as : select a,round(b,2 –comment ) from table,

2. adding “order by” in “IN” clause in “where”.

3. adding user defined function inside sql statement.
ex. select myfunction(a) from table

4. adding “order by” next to “union” two select clause.

5. forget to add comma in between parameter/entries in “decode” statement.

some other things can trigger this error also, but this is because of the bug in oracle version, like in oracle 7 when use use “case” inside in sql query.
or in oracle 10 before 10.2.0.3 using full/left outer join in link with Bug 4433936.
or using quote inside quote, like ‘xxxx’xxxx’xxxx’ as a single string will throw error also, to correct this use the quote inside twice, like: ‘xxxx”xxxx”xxxx’

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s