Oracle ORA-00920 Error: Causes and Solutions Complete Guide

ORA-00920: Invalid Relational Operator — Causes and Fixes

ORA-00920 is a syntax error that Oracle throws when it encounters an invalid or missing relational operator inside a SQL condition clause. This error typically surfaces in WHERE, HAVING, JOIN ON, or CASE WHEN expressions where Oracle's parser expects a valid comparison operator but finds something it cannot recognize. It is especially common in dynamically generated SQL where string concatenation can accidentally produce malformed operator expressions.

---

Top 3 Causes

1. Typo or Wrong Operator (e.g., => instead of >=)

Using operators borrowed from other programming languages or PL/SQL-specific syntax inside a SQL condition is the most frequent cause.

-- ❌ Wrong: => is PL/SQL named parameter syntax, not a comparison operator
SELECT employee_id, salary
FROM   employees
WHERE  salary => 5000;

-- ❌ Wrong: == is not valid in Oracle SQL
SELECT *
FROM   employees
WHERE  department_id == 10;

-- ✅ Correct
SELECT employee_id, salary
FROM   employees
WHERE  salary >= 5000;

SELECT *
FROM   employees
WHERE  department_id = 10;

2. Missing Operator in WHERE Clause or Dynamic SQL

Omitting the comparison operator entirely between a column and a value is another common culprit, particularly when building SQL strings programmatically.

-- ❌ Wrong: operator is missing between column and value
SELECT *
FROM   employees
WHERE  employee_id 100;

-- ✅ Correct
SELECT *
FROM   employees
WHERE  employee_id = 100;

-- ❌ Wrong: dynamic SQL where condition string is accidentally empty
DECLARE
  v_sql VARCHAR2(500);
BEGIN
  -- This produces: "SELECT * FROM employees WHERE "
  v_sql := 'SELECT * FROM employees WHERE ' || '';
  EXECUTE IMMEDIATE v_sql;
END;
/

-- ✅ Correct: use 1=1 as a safe base condition
DECLARE
  v_dept   NUMBER := NULL;
  v_cond   VARCHAR2(200) := '1=1';
  v_sql    VARCHAR2(500);
BEGIN
  IF v_dept IS NOT NULL THEN
    v_cond := v_cond || ' AND department_id = :dept';
  END IF;
  v_sql := 'SELECT employee_id FROM employees WHERE ' || v_cond;
  EXECUTE IMMEDIATE v_sql;
END;
/

3. Incomplete Compound Operator Syntax (BETWEEN, LIKE, IN)

Compound operators like BETWEEN, LIKE, and IN require additional keywords or values to form a complete expression. Dropping any part of the required syntax triggers ORA-00920.

-- ❌ Wrong: BETWEEN without AND and upper bound
SELECT * FROM employees WHERE salary BETWEEN 3000;

-- ❌ Wrong: LIKE without a pattern
SELECT * FROM employees WHERE first_name LIKE;

-- ❌ Wrong: IN without a value list
SELECT * FROM employees WHERE department_id IN;

-- ✅ Correct usage of all three
SELECT e.employee_id,
       e.first_name,
       e.salary
FROM   employees e
WHERE  e.salary       BETWEEN 3000 AND 9000
  AND  e.first_name   LIKE 'A%'
  AND  e.department_id IN (10, 20, 30);

---

Quick Fix Checklist

Symptom	Fix
Used => instead of >=	Replace with correct operator
Used == instead of =	Oracle uses single = for equality
Operator missing between column and value	Add the appropriate operator
BETWEEN with no AND clause	Complete as BETWEEN x AND y
LIKE with no pattern	Add a quoted string pattern
Dynamic SQL producing empty condition	Use 1=1 base + append conditions

---

Prevention Tips

Use bind variables and structured query builders.
Avoid raw string concatenation when generating dynamic SQL. Bind variables prevent both syntax errors and SQL injection. When conditions are optional, always start with a neutral base condition like WHERE 1=1 and append each condition as a complete column operator value block.

-- Safe dynamic SQL with bind variable
DECLARE
  v_sal  NUMBER := 5000;
  v_sql  VARCHAR2(500);
  v_cnt  NUMBER;
BEGIN
  v_sql := 'SELECT COUNT(*) FROM employees WHERE salary >= :1';
  EXECUTE IMMEDIATE v_sql INTO v_cnt USING v_sal;
  DBMS_OUTPUT.PUT_LINE('Count: ' || v_cnt);
END;
/

Validate SQL in your IDE before deployment.
Tools like SQL Developer, Toad, or DBeaver highlight syntax errors in real time. For application-generated SQL, log the final SQL string before execution during development so you can visually inspect the operator placement before it hits the database.

---

Related Errors

• ORA-00936 – missing expression: occurs when the value after an operator is completely absent.
• ORA-00904 – invalid identifier: often appears alongside ORA-00920 when a column name is also misspelled.
• ORA-00933 – SQL command not properly ended: can appear instead of ORA-00920 when compound operator syntax is severely malformed.

---

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