-
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.
-
- It is
important to note that Oracle
MOSC contains
information regarding ORA-00936 in association with
Oracle Bugs:
- 1) Bug#:4567818 base
Bug#:4192148 (unpublished) on 9207
2) Bug#:4212516 (unpublished) on 10.1.0.4.0.
-
- With these, error
ORA-00936 is thrown when the SELECT ON view fails.
Basically, ORA-00936 is
thrownwhen a SQL view is created from "create or replace
view blah as select t.*,other_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, MOSC 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.
Here is another example of
ORA-00936 from
Oracle forums:
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 = ?"
