Friday, May 2, 2008

ORA-00936: missing expression

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 (see case 2 below).

Action: Check the statement syntax and specify the missing component.

1. if you are using prepare statement to connect to oracle with vb or asp you might as get this error as using named parameters. The reason is that OLEDB doesn't support named parameters. e.g.

here i have the example of how to do it

Dim objCmd

Set objCmd = Server.CreateObject("ADODB.Command")
Set objCmd.ActiveConnection = objConn

objCmd.CommandType = adCmdText

objCmd.CommandText = "{CALL procedurename(?,?,?,?,?,?)}"

objCmd.Parameters.Append objCmd.CreateParameter("@a", adVarChar, adParamInput,16)
objCmd.Parameters.Append objCmd.CreateParameter("@b", adVarChar, adParamInput,32)
objCmd.Parameters.Append objCmd.CreateParameter("@c", adDouble, adParamOutput)
objCmd.Parameters.Append objCmd.CreateParameter("@d", adVarChar, adParamOutput,255)
objCmd.Parameters.Append objCmd.CreateParameter("@e", adDouble, adParamOutput)
objCmd.Parameters.Append objCmd.CreateParameter("@f", adDouble, adParamOutput)

objCmd("@a") = sString
objCmd("@b") = sString2


If ERR = 0 Then

o_errnum = objCmd("@c")
o_errStr = objCmd("@d")
o_usertype = objCmd("@e")
o_userid = objCmd("@f")

isValidLogin = True

End If
Set objCmd = Nothing

with this example you can use procedure to pass the paremeter to oracle and at the same time get the output parameters. The keyword is adParamOutput and adParamInput plus you need to specified parameter type make sure they are the same with your oracle procedure. Otherwise you will get an error.

2. If you are not running procedure. The other possibly cause of the error "ORA-00936: missing expression" might be that you tried to execute a SELECT statement and forget to the list of the columns in the SELECT statement.

from tablename;

this also would raise you the same error

So the solution would be "Check the statement syntax and specify the missing component". If you are calling procedure using ? in your preparestatement not @ as I had already said "OLEDB doesn't support named parameters"