ORA-00933: SQL command not properly ended

by | May 8, 2020 | Blog, Oracle

ORA-00933 - SQL command not properly ended

Prior to diving into this error, it’s important we discuss how Oracle processes queries logically and in what order.

Oracle has a couple of rules as to how it executes each query in a specific processing order. Oracle processes a query as below:

  1. Gathers rows based on JOINS and the FROM clause determining the objects the query is running against
  2. Filters out rows based on the WHERE clause
  3. Groups said rows with the GROUP BY clause
  4. Filters groups based on the HAVING clause
  5. Orders the results based on the ORDER BY clause
    NOTE: the ORDER BY clause must contain values from the GROUP BY clause or a group function
  6. Limits the rows based on the LIMIT clause to only return a specific amount of rows

Now that we understand the overall query processing order, the “ORA-00933: SQL command not properly ended” error is caused by a clause in the code that doesn’t fall within the bounds and rules of the order. The error is commonly caused by the following situations.

  • An INSERT statement with an ORDER BY clause. Oracle doesn’t support the inserting of rows in a certain order via the ORDER BY clause — that is done during sorting operations of SELECT queries. To fix this, simply remove the ORDER BY clause. Note: there are cases where you would want the data ordered so you can insert the clause in a subquery, which is common with CREATE VIEW statements.
  • A DELETE statement with an INNER JOIN or ORDER BY clause. Similar to the previous situation, Oracle doesn’t allow ordering rows in a particular fashion to then be deleted. You could resolve the query containing joins by converting the INNER JOIN to WHERE EXISTS (or a subquery). For example:
From:
DELETE FROM t1
INNER JOIN t2 on t1.id = t2.id
WHERE t2.value2 = '...';

To:
DELETE FROM t1
WHERE id in (
    SELECT id
    FROM t2
    WHERE t2.value2 = '...');
  • An UPDATE statement with an INNER JOIN. Similar to the previous situation, Oracle doesn’t allow a join clause on UPDATEs. To resolve this, convert the join to a subquery. For example:
From:
UPDATE t1
SET t1.value1 = t2.value2
INNER JOIN t2 ON t1.id = t2.id;
 
To:
UPDATE t1
SET t1.value1 = (
  SELECT value2
  FROM t2
  WHERE t1.id = t2.id);