📋

Description

The ORA-00904 error indicates that the database cannot find a column or identifier referenced in your SQL query. This typically occurs due to typos, incorrect case sensitivity, or using reserved words without proper quoting.

💬

Error Message

ORA-00904: string : invalid identifier

🔍

Known Causes

4 known causes

⚠️

Typographical Error

A simple typo in the column name or identifier can lead to this error. Double-check spelling and ensure it matches the table definition.

⚠️

Case Sensitivity

Oracle can be case-sensitive depending on how the database and table were created. Ensure the case of the identifier in your query matches the case used when the table was defined, especially if double quotes were used.

⚠️

Reserved Word Usage

Using a reserved word (e.g., 'DATE', 'USER') as a column name without enclosing it in double quotation marks will result in ORA-00904.

⚠️

Missing or Incorrect Alias

If you are using aliases in your query, ensure that you are referencing them correctly and that they are properly defined within the scope of your query.

🛠️

Solutions

4 solutions available

1. Check Column Name Spelling easy

Verify the column name exists and is spelled correctly

1

List all columns in the table

SELECT column_name
FROM user_tab_columns
WHERE table_name = 'EMPLOYEES'
ORDER BY column_id;

2

Common mistakes to check

-- Wrong: column name doesn't exist
SELECT employe_name FROM employees;  -- typo

-- Correct:
SELECT employee_name FROM employees;

2. Quote Case-Sensitive Identifiers easy

Use double quotes for mixed-case or special character column names

1

Use double quotes for case-sensitive names

-- If column was created with quotes:
CREATE TABLE test ("FirstName" VARCHAR2(50));

-- Must query with exact case:
SELECT "FirstName" FROM test;

-- This fails:
SELECT FirstName FROM test;  -- ORA-00904

3. Use Table Alias Correctly easy

When using aliases, reference columns through the alias

1

Fix alias reference

-- Wrong: can't use table name after aliasing
SELECT employees.name
FROM employees e
WHERE e.id = 1;  -- ORA-00904 on 'employees.name'

-- Correct: use the alias
SELECT e.name
FROM employees e
WHERE e.id = 1;

4. Check Column Alias in ORDER BY medium

Column alias may not work in all contexts

1

Use column position or repeat expression

-- This may fail in some contexts:
SELECT first_name || ' ' || last_name AS full_name
FROM employees
WHERE full_name LIKE 'John%';  -- Can't use alias in WHERE

-- Correct: repeat the expression
SELECT first_name || ' ' || last_name AS full_name
FROM employees
WHERE first_name || ' ' || last_name LIKE 'John%';

-- Or use subquery:
SELECT * FROM (
  SELECT first_name || ' ' || last_name AS full_name
  FROM employees
) WHERE full_name LIKE 'John%';

Oracle Invalid Identifier