How To Resolve ORA-00933 SQL Command Not Properly Ended

calendar Feb 22, 2017 · 4 min read · sql-errors  ·
Share on: linkedin copy

Are you getting the ORA-00933: SQL command not properly ended error? Learn what causes it and how to resolve it in this article.

ORA-00933: sql command not properly ended

This error is caused by an SQL statement with a clause that is not allowed for that statement. Some examples that might cause this error are:

  • An INSERT statement with an ORDER BY clause or an INNER JOIN
  • A DELETE statement with an INNER JOIN or ORDER BY clause
  • An UPDATE statement with an INNER JOIN

ORA-00933 Solution

The solution to the ORA-00933 error is to update your query to remove the clause that's causing the issue. This would depend on the type of query being run.

Let's take a look at some example solutions.

While you're here, if you want an easy-to-use list of the main features in Oracle SQL, get my SQL Cheat Sheet here:

INSERT Statement

If you're getting an "ORA-00933 sql command not properly ended" on INSERT, then it could be because:

  • You have a JOIN keyword (such as INNER JOIN, LEFT JOIN) in the query.
  • You have an ORDER BY in the query.

You might have a query that looks like this:

1INSERT INTO student (student_id, first_name, last_name)
2VALUES (20, 'Jack', 'Wheeler')
3ORDER BY student_id;

This statement fails because the INSERT statement does not support ORDER BY. When you insert data, you don't need to specify an order. The data is inserted into the table anyway, and the ORDER BY is only used for SELECT queries.

So, to correct the statement and stop the error, remove the ORDER BY:

1INSERT INTO student (student_id, first_name, last_name)
2VALUES (20, 'Jack', 'Wheeler');

Refer to my guide on the SQL INSERT statement here for more information.

UPDATE Statement

If you're getting the "ORA-00933 sql command not properly ended" on UPDATE, then your query might look like this:

1UPDATE student
2SET student.fees_paid = payment.amount
3INNER JOIN payment ON student.student_id = payment.student_id;

You can't use a JOIN clause in an UPDATE statement. To update a value like this, include the JOIN logic in a  subquery:

1UPDATE student
2SET student.fees_paid = (
3  SELECT amount
4  FROM payment
5  WHERE student.student_id = payment.student_id
6);

This statement should now run without errors.

Read my guide on the SQL UPDATE statement for more information.

DELETE Statement

If you're getting the "ORA-00933 sql command not properly ended" on DELETE, then your query might look like this:

1DELETE FROM student
2WHERE last_name = 'Smith'
3ORDER BY student_id;

The error appears because the ORDER BY does not belong in a DELETE statement. The ORDER BY is only for ordering data returned by a SELECT statement, and serves no purpose in a DELETE statement.

So, change your query to remove the ORDER BY clause:

1DELETE FROM student
2WHERE last_name = 'Smith';

You can refer to my guide on the SQL DELETE statement for more information.

SELECT Statement

Are you getting an "ORA-00933 sql command not properly ended" in a SELECT query?

Well, the SELECT query can support joins and ORDER BY, so what could be causing it?

There can be several reasons for this:

  • Your SELECT statement is using UNION or UNION ALL, and you have an ORDER BY at any point except the end of the query. You can only have an ORDER BY at the end of the query, not within each UNION.
  • You have forgotten a comma in between tables when selecting them.
  • You're running Oracle 8i and trying to use INNER JOIN keywords (or similar join keywords). These were implemented in Oracle 9i.

The exact solution will depend on your SELECT query, but here are a few things you can check:

  • Check that you have the right clauses for your query and are in the right place (e.g. not missing a FROM clause).
  • Check that you're not missing a comma anywhere, such as in the SELECT clause or the FROM clause.
  • Check that you're not missing a bracket anywhere. This can be made easier with SQL Developer's matching bracket highlighting or formatting the SQL to see if something is missing. Other IDEs have similar features.

So, that's how you resolve the ORA-00933 error.

While you're here, if you want an easy-to-use list of the main features in Oracle SQL, get my SQL Cheat Sheet here:

comments powered by Disqus