Error ORA-00907 is a syntax error and therefore, is a commonly seen error by users writing code manually. This error indicates that there is a left parenthesis but no corresponding right parenthesis, or that additional information was contained within the parentheses.

To correct this error, you must find the part of code that contains the missing right parenthesis, insert the missing symbol in the correct spot, and run the statement again.

Error ORA-00907 can commonly occur in commands such as CREATE TABLE, CREATE CLUSTER, and INSERT, which all require an itemized list enclosed in parentheses. It can also occur within subqueries such as WHERE clauses, UPDATE table SET column = (SELECT…) statements.

In the following example, the missing right parenthesis after “20” would throw error ORA-00907:

CREATE TABLE employee

(

employee_name VARCHAR(20 NOT NULL,

employee_phone VARCHAR(10) NOT NULL,

PRIMARY KEY(employee_name)

)

This error also occurs often in cases in which quotation marks are improperly used. If you are using single quotation marks in phrases that are enclosed by other single quotation marks, you must add another single quotation by the inner single quotation. In other words, you cannot use 4 single quotation marks together (‘___’___’___’). It must be written as ‘___”___”___’.

For example, the following lines of code is missing the necessary number of quotation marks:

BEGIN

in_fieldname_list := ‘PROTOTYPE’ ’, ‘ ’ADDR1’ ’, ‘ ’CITY’ ’, ‘ ’STATE’ ’,’ ’ZIP’ ’, ‘ ’OAPPRAISAL’;

It should be:

BEGIN

in_fieldname_list := ‘ ‘ ‘PROTOTYPE’ ’, ‘ ’ADDR1’ ’, ‘ ’CITY’ ’, ‘ ’STATE’ ’,’ ’ZIP’ ’, ‘ ’OAPPRAISAL’ ‘ ’;

Error ORA-00907 specifically points to a missing right parenthesis while error ORA-00906 indicates that there is a missing left parenthesis.

To avoid seeing error ORA-00907, make sure to practice writing correct syntax. One of the easiest ways to ensure your syntax is correct is to use an integrated development environment that includes a source code editor. A good source code editor should offer features such as syntax highlighting, autocomplete, indentation, and matching brackets to help the coder automate the process of coding and make it easier to catch mistakes. With such features, you should be able to easily spot any line of code, which is causing error ORA-00907. If still facing this error after reviewing the syntax, check that it is not being caused by FULL/LEFT OUTER joins associated with Bug 4433936. While the bug has been fixed for later versions of Oracle, it can still be seen in earlier versions.