Did you get an ORA-00936: missing expression error? Learn what it means and how to resolve it in this article.

ORA-00936: missing expression SolutionORA-00936 Cause

The error you’ve gotten is this:

ORA_00936: missing expression

Oracle’s official “cause and action” that appears along with the error is:

Cause: A required part of a clause or expression has been omitted. For example, a SELECT statement may have been entered without a list of columns or expressions or with an incomplete expression. This message is also issued in cases where a reserved word is misused, as in SELECT TABLE.

Action: Check the statement syntax and specify the missing component.

So, in summary, the query is missing some clause that it needs in order to run.

 

ORA-00936 Solution

To resolve the ORA-00936 error:

  1. Check that your column names are all listed correctly in the SELECT clause
  2. Ensure you have a FROM clause in your SELECT statement. Even if you aren’t selecting from a table, you still need FROM in Oracle SQL, so you could use the DUAL table (LINK)
  3. Remove any commas that shouldn’t be in your query

 

Missing Columns

The ORA-00936 error often occurs when you leave out the columns in the SELECT clause.

For example:

SELECT
FROM students;

ORA-00936: missing expression

This is because you need to list the column names after the word SELECT and before the word FROM.

This query should work:

SELECT student_id, first_name, last_name
FROM students;

 

Missing FROM

This error can also occur if you don’t have a FROM keyword in your SELECT statement.

For example, this query will display an error:

SELECT first_name, last_name
WHERE student_id = 5;

There is no FROM clause in this query, so you’ll get an error.

Correct the query to add the FROM clause, so it knows which table to query.

SELECT first_name, last_name
FROM students
WHERE student_id = 5;

Remove Commas

Sometimes you have all of the right keywords, but you’re still getting the ORA-00936: missing expression error.

For example this query gives an error:

SELECT first_name, last_name,
FROM students
WHERE student_id = 5;

The reason for this is because there is a comma after the final column “last_name”, and then there is the FROM keyword.

Commas should only be used when you want to specify another column or table, and not before a keyword like we have in this example.

To correct it, remove the comma.

SELECT first_name, last_name
FROM students
WHERE student_id = 5;

 

ORA-00936 in UPDATE Statement

If you’re getting an ORA-00936: missing expression in an UPDATE statement, then the same steps can be taken:

  1. Check that you have all the keywords that are required (UPDATE, SET)
  2. Check there are no extra commas where there shouldn’t be.
  3. If you’re using a subquery inside the UPDATE statement, then ensure that subquery has all the right keywords and no extra commas as mentioned earlier

 

ORA-00936 in INSERT Statement

Just like the UPDATE statement, you can also get an ORA-00936: missing expression in an INSERT statement.

The same steps can be taken:

  1. Check that you have all of the required keywords.
  2. Check there are no extra commas
  3. Check that the number of values and the number of columns are the same
  4. If you’re using a subquery inside the INSERT statement, then ensure that subquery has all the right keywords and no extra commas as mentioned earlier

 

So, that’s how you resolve the ORA-00936 error in your SQL query. If you have any questions on this error, leave a comment below.

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!

Want an Oracle SQL function cheat sheet for easy reference? Click here to get a PDF file that contains all of Oracle functions for you to reference or print out.

Improve Your Oracle SQL With My 10-Day Email Course

x