oracle tutorial webinars

ORA-00936 Error Message

Some Oracle mistakes are not nearly as intimidating to resolve as the error message would seem to indicate. The ORA-00936 is the perfect example of such a case. This error provides an excellent case where thinking too hard about the answer will cost you far more time and effort than needed.

The ORA-00936 message is a missing expression error in Oracle. All that ‘missing expression’ means is that When attempting to operate a query, a particular part of the clause necessary for it to function was omitted in the text. Stated simply, you left out an important chunk of what you were trying to run. This can happen fairly easily, but provided below are two examples that are the most common occurrence of this issue.

The first example is the product of missing information in a SELECT statement, which triggers the vast majority of ORA-00936 errors. This occurs when entering SELECT on the first line, but then failing to reference the list of columns following the SELECT. If you just enter ‘SELECT’ on line one, and then ‘FROM abc;’ on line two, the ORA-00936 message will be prompted. To fix this, go back and choose a column to input after SELECT so that line one looks something like ‘SELECT distributors_name, distributors_location’, with line two remaining the same. This will correct the error and allow the SELECT statement to process.

Conversely, the error can happen in the latter half of a SQL statement. If the FROM clause within the statement is omitted, the error message will be thrown. You will need to look back at the syntax of the statement and make sure that for items such as SELECT statements, the next line includes a FROM clause (such as ‘FROM list_of_suppliers’) so that the SELECT clause knows where to be triggering information from within the database.

The ORA-00936 error can be prevented by double-checking instances of SQL clauses and making sure that all statements are derived from the proper syntax. This, of course, extends beyond just SELECT statements but also FROM and WHERE statements as well (or any other clause meant to trigger a query). Be positive that any Oracle functions used are spelled out properly, and keep tabs of user-defined functions. It can be easy to mix up user-defined functions and Oracle functions, and doing so can lead to confused syntax that would result in an error message. The representatives at a licensed Oracle consultant firm can work with you to make sure that you understand the difference between these types of functions and have proper knowledge of the individual functions that come pre-equipped with Oracle database software.