FB

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-00933ORA-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.

 

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:

INSERT INTO student (student_id, first_name, last_name)
VALUES (20, 'Jack', 'Wheeler')
ORDER 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:

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

Refer to my guide on the Oracle 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:

UPDATE student
SET student.fees_paid = payment.amount
INNER 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:

UPDATE student
SET student.fees_paid = (
  SELECT amount
  FROM payment
  WHERE student.student_id = payment.student_id
);

This statement should now run without errors.

Read my guide on the Oracle 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:

DELETE FROM student
WHERE last_name = 'Smith'
ORDER 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:

DELETE FROM student
WHERE last_name = 'Smith';

You can refer to my guide on the Oracle 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.

Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!

Get Your FREE PDF: "5 Ways to Avoid SQL Errors"

Get Your FREE PDF: "5 Ways to Avoid SQL Errors"