| |
 |
|
ORA-00907: missing right parenthesis tips
Oracle Error Tips by Burleson Consulting
|
Oracle docs note this about ORA-00907:
ORA-00907
missing right parenthesis
Cause: A left parenthesis has been entered
without a closing right parenthesis, or
extra information was contained in the
parentheses. All parentheses must be entered
in pairs.
Action: Correct the syntax and retry the
statement.
Oracle
offers information regarding ORA-00907 in conjunction with
FULL/LEFT OUTER joins in association with Bug 4433936. This bug causing an ORA-00907
has been fixed in 10.2.0.3 and future versions of 11g, it can still be seen in
9.2.0.5, 9.2.0.7, 9.2.0.8, 10.1.0.5, and 10.2.0.2 in most generic platforms.
The symptoms of ORA-00907 and Bug 4433936 are simple syntax errors which
happen within parsing queries associated with FULL/LEFT OUTER joins. You
can work around ORA-00907 by trying Oracle join operators to rewrite queries, as
opposed to ANSI joins.
If you are using single quotes in phrases that
are surrounded by other single quotes, you have to add another single quote by
the inner single quotes to avoid ORA-00907. For example, you have to use
'___''___''___' instead of '___'___'___' in order for ORA-00907 to not be
thrown over syntax.
Here is an example of a date value with single
quotes, and the format string surrounded by double quotes:
SCOTT@10gXE> CREATE TABLE names
2 (forenames VARCHAR2 (15),
3 dob DATE)
4 /
Table created.
SCOTT@10gXE> INSERT INTO names
2 VALUES ('John', TO_DATE ('01/01/1961', 'dd/mm/yyyy'))
3 /
1 row created.
SCOTT@10gXE> BEGIN
2 CTX_DDL.CREATE_INDEX_SET ('names_iset');
3 CTX_DDL.ADD_INDEX ('names_iset', 'dob');
4 END;
5 /
PL/SQL procedure successfully completed.
SCOTT@10gXE> CREATE INDEX names_idx
2 ON names (forenames)
3 INDEXTYPE IS CTXSYS.CTXCAT
4 PARAMETERS ('INDEX SET names_iset')
5 /
Index created.
SCOTT@10gXE> -- reproduction of error:
SCOTT@10gXE> SELECT COUNT (FORENAMES)
2 FROM NAMES
3 WHERE CATSEARCH
4 (FORENAMES,
5 'JOHN',
6 'DOB BETWEEN TO_DATE ('01/01/1960','dd/mm/yyyy')
7 AND TO_DATE ('01/01/1962','dd/mm/yyyy')') > 0
8 /
'DOB BETWEEN TO_DATE ('01/01/1960','dd/mm/yyyy')
*
ERROR at line 6:
ORA-00907: missing right parenthesis
SCOTT@10gXE> -- correction:
SCOTT@10gXE> SELECT COUNT(FORENAMES)
2 FROM NAMES
3 WHERE CATSEARCH
4 (FORENAMES,
5 'JOHN',
6 'DOB BETWEEN TO_DATE
(''01/01/1960'',''dd/mm/yyyy'')
7 AND TO_DATE
(''01/01/1962'',''dd/mm/yyyy'')') > 0
8 /
COUNT(FORENAMES)
----------------
1
SCOTT@10gXE>
|
|
|
|
|
|
Burleson is the American Team
Note:
This Oracle
documentation was created as a support and Oracle training reference for use by our
DBA performance tuning consulting professionals.
Feel free to ask questions on our
Oracle forum.
Verify
experience!
Anyone
considering using the services of an Oracle support expert should
independently investigate their credentials and experience, and not rely on
advertisements and self-proclaimed expertise. All legitimate Oracle experts
publish
their Oracle
qualifications.
Errata?
Oracle technology is changing and we
strive to update our BC Oracle support information. If you find an error
or have a suggestion for improving our content, we would appreciate your
feedback. Just
e-mail:
 and include the URL for the page.
Copyright © 1996 - 2020
All rights reserved by
Burleson
Oracle ®
is the registered trademark of Oracle Corporation.
|
|
