Oracle ORA-01710 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-01710: duplicate keyword — Causes, Fixes, and Prevention

What Is ORA-01710?

ORA-01710 is a syntax error thrown by the Oracle parser when the same keyword appears more than once within a single SQL statement where it is only permitted once. This commonly occurs in DDL statements like CREATE TABLE or CREATE INDEX, privilege commands like GRANT, or complex DML queries where clauses are accidentally duplicated. The fix is straightforward once you identify the offending keyword — simply remove the duplicate occurrence.

Top 3 Causes

1. Duplicate Storage or Physical Attribute Keywords in DDL

When writing CREATE TABLE or CREATE INDEX statements, repeating storage-related keywords such as PCTFREE, INITIAL, or INITRANS triggers ORA-01710. This often happens when merging scripts from different sources or copy-pasting DDL blocks.

-- ❌ Incorrect: PCTFREE specified twice
CREATE TABLE orders (
    order_id   NUMBER(10),
    order_date DATE
)
PCTFREE 10
PCTUSED 40
PCTFREE 20;   -- Duplicate keyword → ORA-01710

-- ✅ Correct: each keyword appears only once
CREATE TABLE orders (
    order_id   NUMBER(10),
    order_date DATE
)
PCTFREE 10
PCTUSED 40
INITRANS 2;

2. Duplicate Option Keywords in GRANT Statements

Using WITH GRANT OPTION or WITH ADMIN OPTION more than once in a single GRANT statement will raise ORA-01710. This typically results from scripting bugs or manual copy-paste errors in privilege management scripts.

-- ❌ Incorrect: WITH GRANT OPTION duplicated
GRANT SELECT ON hr.employees TO app_user
WITH GRANT OPTION
WITH GRANT OPTION;   -- ORA-01710

-- ✅ Correct
GRANT SELECT ON hr.employees TO app_user
WITH GRANT OPTION;

-- ❌ Incorrect: WITH ADMIN OPTION duplicated
GRANT CREATE SESSION TO reporting_user
WITH ADMIN OPTION
WITH ADMIN OPTION;   -- ORA-01710

-- ✅ Correct
GRANT CREATE SESSION TO reporting_user
WITH ADMIN OPTION;

3. Duplicate Clauses in SELECT Statements (Dynamic SQL)

Repeating clauses like ORDER BY or GROUP BY in a single query — often introduced by dynamic SQL builders in application code — will also cause ORA-01710.

-- ❌ Incorrect: ORDER BY appears twice
SELECT employee_id, last_name, salary
FROM   employees
WHERE  department_id = 50
ORDER BY salary DESC
ORDER BY last_name ASC;   -- ORA-01710

-- ✅ Correct: combine sort criteria in one ORDER BY
SELECT employee_id, last_name, salary
FROM   employees
WHERE  department_id = 50
ORDER BY salary DESC, last_name ASC;

-- ❌ Incorrect: GROUP BY duplicated
SELECT department_id, COUNT(*) AS headcount
FROM   employees
GROUP BY department_id
GROUP BY department_id;   -- ORA-01710

-- ✅ Correct
SELECT department_id, COUNT(*) AS headcount
FROM   employees
GROUP BY department_id;

Quick Fix Solutions

Read the full error message carefully — Oracle usually highlights the position of the problem token, making it easier to locate the duplicate.
Search and replace — In your SQL editor, search for the suspected keyword (e.g., PCTFREE, WITH GRANT OPTION, ORDER BY) and count its occurrences in the statement.
Use EXPLAIN PLAN before executing long scripts to catch syntax errors without side effects.

-- Pre-validate syntax with EXPLAIN PLAN
EXPLAIN PLAN FOR
SELECT department_id, COUNT(*)
FROM   employees
GROUP BY department_id
GROUP BY department_id;   -- ORA-01710 caught before actual execution

Prevention Tips

Lint your SQL scripts before execution using tools like Oracle SQL Developer, Toad, or SQLcl. Their built-in syntax checkers detect duplicate keywords before any damage is done.
Use SQL Builder patterns in application code — When generating dynamic SQL programmatically (Java, Python, etc.), use builder classes or flag variables to ensure each clause is appended only once. This prevents ORA-01710 and other syntax errors at the source.

Related Oracle Errors

Error Code	Description
ORA-00905	Missing keyword — the opposite problem (too few keywords)
ORA-00936	Missing expression — another common syntax error
ORA-00907	Missing right parenthesis — often appears alongside DDL issues

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

DEV Community