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:
- Gathers rows based on JOINS and the FROM clause determining the objects the query is running against
- Filters out rows based on the WHERE clause
- Groups said rows with the GROUP BY clause
- Filters groups based on the HAVING clause
- 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
- 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);