Selectiing an Arithmetic Expression from Dual May Fail With: ORA-00936: missing expression

Last updated on APRIL 25, 2013

Applies to:

Symptoms

Attempting to execute the following simple query:

select 4 - .2 from dual

fails with:

ORA-00936: missing expression



Executing .NET code using ODP similar to the following demostrates the issue:

Dim conn As Oracle.DataAccess.Client.OracleConnection = New Oracle.DataAccess.Client.OracleConnection(My.Settings.oraconn)
conn.Open()
Dim da As Oracle.DataAccess.Client.OracleDataAdapter
Dim ds As New DataSet
da = New Oracle.DataAccess.Client.OracleDataAdapter("select 4 - .2 from dual", conn)
da.Fill(ds)
MsgBox(ds.Tables(0).Rows(0)(0))



A 100046 trace that is generated when the error is generated shows the SQL:

select 4-. 2 from dual

ODP.NET changes the SQL and strips out spacing causing the exception.

Executing the same bad SQL from SQL PLUS shows the same error:

SQL> select select 4-. 2 from dual;
select select 4-. 2 from dual
       *
ERROR at line 1:
ORA-00936: missing expression

Cause