Quick Tips to resolve the ORA-00936 missing expression

Description

ORA-00936 : missing expression is one of the common error everybody working in Oracle SQL must have faced some time. This generally happens when you omit important thing in the Sql statement i.e you left out an important chunk of what you were trying to run

Reference : Oracle documentation

Cause of ORA-00936 : missing expression

This Oracle error is mainly related to the SQL SELECT statements. One obvious reason is select column list is missing or expressions in the selected columns are incomplete.

Check list to run to resolve the ORA-00936 missing expression error

(1) It happens when you forget to list the column in the select statement

Select from mrp_details;

select from mrp_details;
ERROR at line 1:
ORA-00936: missing expression

The correct way would be list the column you want to select

Select col1,col2 from mrp_details;

(2) We sometimes makes mistake in the usage of Distinct statement. Following statement will fail with ORA-00936

select distinct a, b,c,d, distinct e from tab_example
where b=’ABCD’ and c =1 and d= ‘JOHN’
ERROR at line 1:
ORA-00936: missing expression

Having two distinct clause does not make sense and give error

Another example

select a, b,c,d, distinct e from tab_example
where b=’ABCD’ and c =1 and d= ‘JOHN’
ERROR at line 1: ORA-00936: missing expression

distinct can be used in the starting only

So correct statement would be

select distinct a, b,c,d, e from tab_example
where b=’ABCD’ and c =1 and d= ‘JOHN’

(3) This error is caused when part of the expression is omitted , some examples are

select 2**8 from dual; 
select 2**8 from dual;
ERROR at line 1: ORA-00936: missing expression

** operators works in PLSQL but not in SQL, We need to use Power function for it, So correct way would be

select power(2,3) from dual;
POWER(2,3)
--------
8

(4) Another example

select dept_name||' '|| from dept;
select dept_name||' '|| from dept
ERROR at line 1: ORA-00936: missing expression

Here you forget to mention column name after the concatenation operator, the correct SQL would be

select dept_name||' '||dept_no from dept;

(5) When you add extra commas in the list of column

select dept_no, dept_name, ,dept_location from dept_table;
select dept_no, dept_name, ,dept_location from dept_table;
ERROR at line 1: ORA-00936: missing expression

So we need to double check the SQL statement when we hit this error and make sure we are doing the common mistake

(6) This error will also come if you omit the From in the SQL statement

select dept_no, dept_name, ,dept_location where dept_name like ‘A%’;
select dept_no, dept_name, ,dept_location where dept_name like ‘A%’;
ERROR at line 1: ORA-00936: missing expression

Here we missed to mention the from clause.SELECT statement has three parts: to wit: “SELECT->FROM->WHERE
You can omit where clause but select and from are necessary

select dept_no, dept_name, ,dept_location from dept_table where dept_name like ‘A%’;

(7) It can also occurs in insert statement like below

insert into table1 (col1,col2) values as select col1,col2 from table2;
ERROR at line 1: ORA-00936: missing expression

We don’t need values as in this statement

insert into table1 (col1,col2) select col1,col2 from table2;

(8) We can sometimes mix up user-defined functions and Oracle functions, and doing so can lead to confused syntax that would result in an error message.So avoid them

(9) There are Oracle some bugs also
(a) Bug:4567818 base Bug#:4192148 – unpublished on 9207
(b) Bug:4212516 (unpublished) on oracle 10.1.0.4.0.
With these bugs, ORA-00936 error is thrown when the SELECT ON view fails. Basically, ORA-00936 is thrown when a SQL view is created from “create or replace view MY_VIEW as select t.*,other_tab_col from tab t, other_tab”.This creates a view definition that is incorrect in the DBA_VIEWS, thus throwing ORA-00936 and possible core dumps.In order to fix the bugs and resolve ORA-00936, MetaLink offers these solutions for the appropriate version:
Fix for 9.2.0.7 :Patch 4192148 is available for Solaris (64bit) and AIX5L Based Systems (64-bit).Fix for 10.1.0.4 :
Patch 4212516 is available for most of the platforms.

In nutshell, ORA-00936 missing expression can be resolved by carefully checking your SQL statement.

Description

Cause of ORA-00936 : missing expression

Check list to run to resolve the ORA-00936 missing expression error

Reader Interactions

Leave a Reply Cancel reply