ORA-00920: Invalid Relational Operator - Fix WHERE Clause

ORA-00920: Invalid Relational Operator

Error Overview

Error Text: ORA-00920: invalid relational operator

The ORA-00920 error is raised when Oracle’s SQL parser encounters a comparison operator that it cannot recognize or that is used incorrectly in a WHERE clause, JOIN condition, HAVING clause, or CHECK constraint. This error typically points to unsupported operator syntax, misplaced keywords, or incomplete condition expressions.

Common Causes

1. Non-Oracle Operator Syntax

• Using != is valid in Oracle, but !> or !< are not (SQL Server-specific)
• Using <> is valid; using >> or other double-character variants is not
• Using # or @ as comparison tokens

2. Incorrect NULL Comparison

• Using = NULL or != NULL instead of IS NULL / IS NOT NULL
• This can trigger ORA-00920 in some Oracle versions and contexts

3. Operator Placed in Wrong Position

• Condition written as value = column when operator position is ambiguous and surrounding syntax creates a parse conflict
• Writing WHERE AND column = value (dangling AND with no left operand)

4. Incomplete WHERE Clause After Keyword

• WHERE column BETWEEN without both bounds
• WHERE column LIKE without the pattern string
• WHERE column IN without the list or subquery

5. Extra or Missing Spaces Around Operators

• Operators like > = (with a space) instead of >=
• Operators split across lines in dynamic SQL concatenation

Diagnostic Queries

Identify Failing Statements

-- Review recently failed parse attempts in the shared pool
SELECT
  sql_id,
  sql_text,
  parse_calls,
  executions,
  last_active_time
FROM v$sql
WHERE parse_calls > 0
  AND executions = 0
  AND last_active_time > SYSDATE - 1/24
ORDER BY last_active_time DESC
FETCH FIRST 20 ROWS ONLY;

Check Audit Trail for ORA-00920 Events

SELECT
  event_timestamp,
  db_user_name,
  sql_text,
  return_code
FROM unified_audit_trail
WHERE return_code = 920
  AND event_timestamp > SYSTIMESTAMP - INTERVAL '1' DAY
ORDER BY event_timestamp DESC;

Check Alert Log for Application Errors

SELECT
  originating_timestamp,
  message_text
FROM v$diag_alert_ext
WHERE message_text LIKE '%ORA-00920%'
  AND originating_timestamp > SYSTIMESTAMP - INTERVAL '24' HOUR
ORDER BY originating_timestamp DESC;

Use DBMS_SQL to Validate the Condition

DECLARE
  v_cursor INTEGER;
  v_sql    VARCHAR2(4000) :=
    'SELECT * FROM employees WHERE salary = NULL';  -- invalid null comparison
BEGIN
  v_cursor := DBMS_SQL.OPEN_CURSOR;
  BEGIN
    DBMS_SQL.PARSE(v_cursor, v_sql, DBMS_SQL.NATIVE);
    DBMS_OUTPUT.PUT_LINE('SQL is valid');
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
  END;
  DBMS_SQL.CLOSE_CURSOR(v_cursor);
END;
/

Step-by-Step Resolution

1. Replace Non-Oracle Operators

-- WRONG: SQL Server-specific non-standard operators
-- SELECT * FROM employees WHERE salary !> 50000;
-- SELECT * FROM employees WHERE hire_date !< DATE '2020-01-01';

-- CORRECT: Oracle-standard operators
SELECT * FROM employees WHERE salary <= 50000;
SELECT * FROM employees WHERE hire_date >= DATE '2020-01-01';

-- Oracle-supported comparison operators:
-- =    equal
-- !=   not equal (also: <>, ^=)
-- >    greater than
-- <    less than
-- >=   greater than or equal
-- <=   less than or equal

2. Fix NULL Comparisons

-- WRONG: Using = or != for NULL comparison
-- SELECT * FROM employees WHERE commission_pct = NULL;
-- SELECT * FROM employees WHERE commission_pct != NULL;

-- CORRECT: Use IS NULL / IS NOT NULL
SELECT * FROM employees WHERE commission_pct IS NULL;
SELECT * FROM employees WHERE commission_pct IS NOT NULL;

-- For NVL-based comparison patterns:
SELECT * FROM employees
WHERE NVL(commission_pct, 0) > 0.1;

3. Fix Incomplete WHERE Conditions

-- WRONG: BETWEEN without second bound
-- SELECT * FROM employees WHERE salary BETWEEN 40000;

-- CORRECT
SELECT * FROM employees WHERE salary BETWEEN 40000 AND 80000;

-- WRONG: LIKE without a pattern
-- SELECT * FROM employees WHERE last_name LIKE;

-- CORRECT
SELECT * FROM employees WHERE last_name LIKE 'S%';

-- WRONG: Dangling AND with no left operand
-- SELECT * FROM employees WHERE AND department_id = 10;

-- CORRECT
SELECT * FROM employees WHERE department_id = 10;

4. Fix Operators Split by Spaces in Dynamic SQL

-- WRONG: Space inside '>=' operator during string concatenation
DECLARE
  v_op  VARCHAR2(10) := '> ';  -- accidental trailing space before =
  v_sql VARCHAR2(200) := 'SELECT * FROM employees WHERE salary ' || v_op || '= 50000';
BEGIN
  -- Results in: WHERE salary > = 50000 -- invalid
  EXECUTE IMMEDIATE v_sql;
END;
/

-- CORRECT: Use complete operator token
DECLARE
  v_op  VARCHAR2(10) := '>=';  -- correct two-character token
  v_sql VARCHAR2(200) := 'SELECT * FROM employees WHERE salary ' || v_op || ' 50000';
BEGIN
  EXECUTE IMMEDIATE v_sql;
END;
/

5. Fix Join Conditions with Invalid Operators

-- WRONG: Using assignment-style operator in join
-- SELECT e.last_name, d.department_name
-- FROM employees e, departments d
-- WHERE e.department_id := d.department_id;  -- := is PL/SQL assignment, not SQL

-- CORRECT
SELECT e.last_name, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id;

-- CORRECT: ANSI-style join (preferred)
SELECT e.last_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

6. Fix CHECK Constraint with Invalid Operator

-- WRONG: Using unsupported operator in check constraint
-- ALTER TABLE employees
-- ADD CONSTRAINT chk_sal CHECK (salary !< 0);

-- CORRECT
ALTER TABLE employees
ADD CONSTRAINT chk_sal CHECK (salary >= 0);

-- More complex check constraint example
ALTER TABLE orders
ADD CONSTRAINT chk_dates CHECK (ship_date >= order_date);

7. Fix HAVING Clause Operator Issues

-- WRONG: Invalid operator in HAVING clause
-- SELECT department_id, AVG(salary)
-- FROM employees
-- GROUP BY department_id
-- HAVING AVG(salary) => 60000;  -- => is not a valid Oracle operator

-- CORRECT
SELECT department_id, AVG(salary) AS avg_sal
FROM employees
GROUP BY department_id
HAVING AVG(salary) >= 60000;

Prevention Strategies

1. Use a Standard Operator Reference

-- Oracle-supported SQL comparison operators reference
-- Single-row comparisons:
--   =, !=, <>, ^=, >, <, >=, <=
-- Multi-row / set comparisons:
--   IN, NOT IN, ANY, SOME, ALL
-- Range comparisons:
--   BETWEEN ... AND ...
-- Pattern matching:
--   LIKE, NOT LIKE (with % and _ wildcards)
-- NULL comparisons:
--   IS NULL, IS NOT NULL
-- Existence:
--   EXISTS, NOT EXISTS

-- Validate your operators in SQL Developer before deployment
SELECT 1 FROM dual WHERE 1 >= 0;  -- quick sanity check

2. Parameterize Conditions Instead of Dynamic Operator Injection

-- RISKY: Injecting operators via string concatenation
-- v_where := 'salary ' || v_op || ' ' || v_val;  -- op could be invalid

-- SAFER: Use parameterized comparisons with fixed operators
CREATE OR REPLACE PROCEDURE get_employees_by_salary (
  p_mode   IN VARCHAR2,   -- 'GT', 'LT', 'EQ'
  p_salary IN NUMBER
) AS
  v_sql VARCHAR2(500);
BEGIN
  IF p_mode = 'GT' THEN
    v_sql := 'SELECT * FROM employees WHERE salary > :1';
  ELSIF p_mode = 'LT' THEN
    v_sql := 'SELECT * FROM employees WHERE salary < :1';
  ELSE
    v_sql := 'SELECT * FROM employees WHERE salary = :1';
  END IF;
  EXECUTE IMMEDIATE v_sql USING p_salary;
END;
/

3. Enforce NULL-Safe Comparisons in Code Standards

-- Coding standard: always use IS NULL / IS NOT NULL for null checks
-- Never use = NULL or != NULL

-- Utility function to build null-safe conditions
CREATE OR REPLACE FUNCTION null_safe_eq (
  p_col   IN VARCHAR2,
  p_val   IN VARCHAR2
) RETURN VARCHAR2 AS
BEGIN
  IF p_val IS NULL THEN
    RETURN p_col || ' IS NULL';
  ELSE
    RETURN p_col || ' = ''' || p_val || '''';
  END IF;
END;
/

4. Peer Review Checklist for WHERE Clauses

• All comparison operators are from the Oracle-supported list
• NULL checks use IS NULL / IS NOT NULL, not = or !=
• BETWEEN expressions have both lower and upper bounds
• LIKE expressions have a pattern string
• IN lists are enclosed in parentheses
• No operators split by spaces in dynamic SQL

Related Errors

• ORA-00936 - Missing expression
• ORA-00907 - Missing right parenthesis
• ORA-00933 - SQL command not properly ended
• ORA-01722 - Invalid number

Emergency Response

Quick Fixes

1. Test the WHERE clause in isolation

   -- Strip to SELECT 1 FROM dual WHERE <your condition>
   SELECT 1 FROM dual WHERE 1 >= 0;  -- replace with your condition

2. Check for non-printable characters in the SQL string

   SELECT DUMP(sql_text) FROM v$sql WHERE sql_id = '&sql_id';
   -- Non-printable characters appear as decimal codes and may masquerade as invalid operators

3. Rebuild the condition from scratch

   -- When the source is a complex dynamic SQL builder,
   -- log the final SQL text before execution
   DBMS_OUTPUT.PUT_LINE('Executing: ' || SUBSTR(v_sql, 1, 4000));

Post-Resolution Cleanup

-- Confirm the fixed statement executes cleanly
EXPLAIN PLAN FOR
SELECT * FROM employees WHERE salary >= 50000 AND commission_pct IS NOT NULL;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- Recompile any stored objects that used the invalid operator
ALTER PACKAGE my_package COMPILE;

-- Flush shared pool if corrupted cursor plans are suspected
ALTER SYSTEM FLUSH SHARED_POOL;