DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-00964 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-00964: Table Name Not in FROM List — Causes, Fixes & Prevention

ORA-00964 is a SQL parsing error thrown by Oracle Database when a table name referenced in a query clause (such as WHERE, SELECT, or HAVING) does not appear in the FROM clause of that query or subquery. Oracle strictly requires every table or view used in a query to be declared in the FROM clause, and any violation is caught immediately at parse time before execution begins. This error is common in complex multi-table joins and subquery scenarios.

Top 3 Causes

1. Missing Table in the FROM Clause

The most frequent cause: a table is referenced in the WHERE or SELECT clause but was simply forgotten in the FROM clause.

-- ❌ Error: DEPARTMENTS is referenced but not in FROM
SELECT e.employee_id,
       e.first_name,
       d.department_name
FROM   employees e
WHERE  d.department_id = e.department_id
AND    e.salary > 5000;

-- ✅ Fix: Add DEPARTMENTS to the FROM clause with a JOIN
SELECT e.employee_id,
       e.first_name,
       d.department_name
FROM   employees   e
JOIN   departments d ON d.department_id = e.department_id
WHERE  e.salary > 5000;
Enter fullscreen mode Exit fullscreen mode

2. Incorrect Table Reference Inside a Subquery

When writing subqueries, referencing a table inside the subquery that belongs only to the outer query's FROM clause (without proper correlation) causes ORA-00964.

-- ❌ Error: DEPARTMENTS is not in the subquery's FROM clause
SELECT employee_id, salary
FROM   employees
WHERE  salary > (SELECT AVG(salary)
                 FROM   employees
                 WHERE  departments.location_id = 1700);

-- ✅ Fix: Properly join DEPARTMENTS inside the subquery
SELECT employee_id, salary
FROM   employees
WHERE  salary > (SELECT AVG(e2.salary)
                 FROM   employees   e2
                 JOIN   departments d ON d.department_id = e2.department_id
                 WHERE  d.location_id = 1700);
Enter fullscreen mode Exit fullscreen mode

3. Mismatched or Undefined Table Alias

Declaring one alias in the FROM clause but using a different (undefined) alias in another clause is a very common mistake, especially when editing legacy SQL.

-- ❌ Error: FROM clause uses alias 'e', but WHERE clause uses undefined alias 'EMP'
SELECT e.employee_id,
       e.last_name
FROM   employees e
WHERE  EMP.department_id = 10
AND    EMP.salary > 3000;

-- ✅ Fix: Use the declared alias 'e' consistently
SELECT e.employee_id,
       e.last_name
FROM   employees e
WHERE  e.department_id = 10
AND    e.salary > 3000;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Checklist

When you encounter ORA-00964, follow these steps:

  1. Read the error carefully — Oracle may indicate the problematic table name in the error message.
  2. List all tables used in the query — Check every clause (SELECT, WHERE, GROUP BY, HAVING, ORDER BY) for table/alias references.
  3. Compare against the FROM clause — Every referenced table or alias must appear in FROM.
  4. Check subquery scope — Each subquery has its own FROM scope. Tables from the outer query must be properly referenced using correlated subquery syntax. 
-- Diagnostic: Use EXPLAIN PLAN to catch issues early
EXPLAIN PLAN FOR
SELECT e.employee_id, d.department_name
FROM   employees   e
JOIN   departments d ON d.department_id = e.department_id
WHERE  e.salary > 5000;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

  • Write the FROM clause first. Before writing SELECT or WHERE, define all tables and their aliases in FROM. This forces you to think about the data sources upfront and eliminates accidental omissions.
  • Use a SQL linting tool. IDEs like SQL Developer, Toad, or DBeaver provide syntax highlighting and real-time error detection. Integrate SQL static analysis into your CI/CD pipeline to catch ORA-00964 before code reaches production.

Related Oracle Errors

Error Code Description
ORA-00904 Invalid identifier — often triggered alongside ORA-00964 when a column from a missing table cannot be resolved.
ORA-00942 Table or view does not exist — similar symptom but caused by a missing or inaccessible object, not a FROM clause omission.
ORA-00918 Column ambiguously defined — occurs in multi-table joins when the same column name exists in multiple tables without alias qualification.

📖 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.

Top comments (0)