ORA-00936:missing expression
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.
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.
Example:
One Simple example is
SELECT FROM EMPLOYEE; raise ORA-00936 error as the column list is missing..
This ORA-00936 may also caused with respective to the usage of the exponential operator “**” in the SQL statements. It may result the following errors:
ORA 936 missing expression
PLS-936
ORA 6550 line
ORA-00936 Exponential Operator Scenario:
Symptoms
Using the exponential operator '**' in a SQL statement results in ORA-936 or PLS-936 depending on whether the SQL statement is executed from SQLPLUS prompt or within a PL/SQL block.
SQL> select 2**2 from dual;
select 2**2 from dual
*
ERROR at line 1:
ORA-00936: missing expression
SQL> declare
2 x number := 2;
3 y number := 4;
4 z number;
5 begin
6 select x**y into z from dual;
7 end;
8 /
y number := 4;
*
ERROR at line 3:
ORA-06550: line 6, column 10:
PL/SQL: ORA-00936: missing expression
ORA-06550: line 6, column 1:
PL/SQL: SQL Statement ignored
Cause
'**' is a valid exponential character in PL/SQL and cannot be used in a SQL statement. The exponential operator '**' can be used in PL/SQL statements:
SQL> declare
2 x number := 2;
3 y number := 4;
4 z number;
5 begin
6 z := x**y;
7 end;
8 /
PL/SQL procedure successfully completed.
Fix
Use power() function instead of '**' in SQL statement'.
SQL> select power(2,2) from dual;
POWER(2,2)
----------
4
SQL> declare
2 x number := 2;
3 y number := 4;
4 z number;
5 begin
6 select power(x,y) into z from dual;
7 end;
8 /
PL/SQL procedure successfully completed.
Oracle Bugs related to ORA-00936 error:
There are also a few oracle bugs related to ORA-00936. Listed below are two bugs related to ORA-00936. You can find complete list of bugs at Oracle Metalink.
1) Bug#:4567818 base Bug#:4192148 (unpublished) on 9207
2) Bug#:4212516 (unpublished) on 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.
ORA-00936 Example from Oracle Forum:
Question:
When trying o use the code below, I keep getting ORA-00936. What is the problem?
Dim sSQL As String = _
"UPDATE TBLICPMETHODS SET Frequency = @Frequency, " & _
"Calibration = @Calibration, QCValue = @QCVAlue" & _
" WHERE Frequency = '" & Session("EditFrequency") & "' AND MethodNumber = " & ddlMethods.SelectedValue
Dim connConnection As New OleDbConnection(Application("ERSconnectionStr"))
connConnection.Open()
Dim cmdCommand As New OleDbCommand(sSQL, connConnection)
Dim prmFrequency As OleDbParameter = _
New OleDbParameter("@Frequency", OleDbType.VarChar, 75)
prmFrequency.Value = sFrequency
cmdCommand.Parameters.Add(prmFrequency)
Dim prmCalibration As OleDbParameter = _
New OleDbParameter("@Calibration", OleDbType.Double, 75)
prmCalibration.Value = CDbl(sCalibration)
cmdCommand.Parameters.Add(prmCalibration)
Dim prmQCValue As OleDbParameter = _
New OleDbParameter("@QCValue", OleDbType.Double, 75)
prmQCValue.Value = CDbl(sQCValue)
cmdCommand.Parameters.Add(prmQCValue)
' MessageBox.show(cmdCommand.CommandText + ":Frequency=" + e.Item.Cells(2).Text + ":Calibration=" + e.Item.Cells(3).Text + ":QCValue=" + e.Item.Cells(4).Text)
Try
cmdCommand.ExecuteNonQuery()
Catch ex As OleDbException
MessageBox.show(ex.Message)
End Try
Answer:
What you are forgetting is that in OLEDB you cannot use named parameters. This being, your use of @parameter is throwing ORA-00936. Trying taking out the @ and using a question mark instead to clear up ORA-00936. Here is an example, instead of:
"UPDATE TBLICPMETHODS SET Frequency = @Frequency, " & _
"Calibration = @Calibration, QCValue = @QCVAlue"
use:
"UPDATE TBLICPMETHODS SET Frequency = ?, " & _
"Calibration = ?, QCValue = ?"
- By Mendus
