Go ORA-00907: Missing closing parenthesis

Go to: http://www.cnblogs.com/Olive116/p/5149680.html

ORA-00907: Missing closing parenthesis preface

The recent use of Oracle database in the development process, the query data in the program encountered the "ORA-00907: Missing closing parenthesis" problem, but if the SQL statement directly in the database or PL, but can be normal query, in order to solve this problem, toss a half day, Found some information, so the various causes of the "missing closing parenthesis" situation was summarized. The wording is as follows.

1 UNION All in order by causes missing closing parenthesis

The use of order by in a subquery with union ALL causes the error to be missing the closing parenthesis, in fact there is no need to sort in a subquery with union all, since the union then makes up a new set, and the previous sort is useless for the new collection. The new collection after the union is directly queried and then sorted.

Examples are as follows:

SELECT * FROM

(

Select Column_a,column_b

From Table_example_a

ORDER BY Column_a

UNION ALL

Select Column_a,column_b

From Table_example_b

ORDER BY Column_a

A

The solution is as follows:

SELECT * FROM

(

Select Column_a,column_b

From Table_example_a

UNION ALL

Select Column_a,column_b

From Table_example_b

A

ORDER BY Column_a

2. In (subquery) usage, the subquery cannot use the order by!

This situation is similar to the one depicted in 1, the first in (subquery) usage using ORDER by will be error, second, the subquery with order BY, purely superfluous, the purpose of the subquery, just to find the appropriate data. If you want to sort, you can do it in the outer row.

Examples are as follows:

SELECT * from Tabel_example where ID in (select ID from Table_example where id>500 oder by ID DESC)

The solution is as follows:

SELECT * from Tabel_example where ID in (select ID from Table_example where id>500) oder by ID DESC

3. When you create a table, the prompt is missing and parentheses 3.1 when you create the table, the keyword sequence is incorrect.

Examples are as follows:

CREATE TABLE T_example (
ID Serial primary Key,
t_id int NOT NULL default 0

)

The solution is as follows:

CREATE TABLE T_example (
ID Serial primary Key,
t_id int default 0 NOT NULL

)

3.2 fields in a table are keywords when you create a table

Examples are as follows:

CREATE Tbale T_example
(
ID Number (18,0) is not NULL,
Desc varchar () NOT NULL

)

The solution is as follows:

Add double quotation marks to the corresponding keyword

CREATE Tbale T_example
(
ID Number (18,0) is not NULL,
"desc" varchar () NOT NULL

)

3.3 Setting the length of a field in a table is not set when the table is created

Examples are as follows:

CREATE tabel t_example

(

ID bigint not NULL PRIMARY KEY,
Name varchar not NULL

)

The solution is as follows:

CREATE tabel t_example

(

ID bigint not NULL PRIMARY KEY,
Name varchar ($) Not NULL

)

3.4 Foreign key types do not exactly match the primary key type when creating a primary foreign KEY constraint

Error when the primary foreign key type is not exactly the same when the closing parenthesis is missing

4. A missing closing parenthesis error occurred in SQL query

The reason that a missing closing parenthesis error is thrown on a query is mostly due to the conversion and filtering of dates in the query statement. Most of the time, there is less single quotation marks for conversions about date types.

Examples are as follows:

SELECT * from T_example Where t_date in (2015-01-20 22:37)

The solution is as follows:

SELECT * from T_example Where t_date in (' 2015-01-20 22:37 ')

There is also the case that when converting time in the where filter, it is sometimes possible to do it directly in Sql/plus, but there are errors in the program.

Examples are as follows:

SELECT * from T_example Where

ID in (the Select ID from T_example_b where d_date>=to_date (' 2015-01-20 ', ' yyyy-mm-dd '))

This statement is not a problem in the database execution directly, but in the program to execute the incoming date parameter is sometimes reported missing closing parenthesis error, in order to solve this problem, we can change the format of the passed date parameter , as follows:

SELECT * from T_example Where

ID in (SELECT ID from T_example_b where d_date>=to_date (20150120, ' yyyy-mm-dd '))

There is also an error about the Oracle Date format:ORA-01840: The input value is not long enough for the date format

Examples are as follows: Select To_date (2015-01-01, ' Yyyy-mm-dd ') from dual

To solve this problem, we can also use the format of changing the date parameters passed in, as follows:

Select To_date (20150101, ' Yyyy-mm-dd ') from dual

5. Other causes of missing closing parenthesis 5.1 write error, indeed missing a parenthesis 5.2 field name missing double quotation marks

This problem does not exist if the table field names are all uppercase when you create the tables

Go ORA-00907: Missing closing parenthesis