Oracle data access error: ORA-00936: missing expression

Question

I am accessing an Oracle database in my asp.net application, and am getting this error:

ORA-00936: missing expression

My c# code is:

getInfoByPoNum = 
"SELECT h.SYS_HEADER_ID, 
    h.FOLIO1 AS INV_NUMBER, 
    v.VENDOR_NAME,
    CASE WHEN h.Comments LIKE '%CLOSED%' THEN 'CLOSED' ELSE NVL(h.Comments, 'OPEN') END AS CComments,
    h.ORG_ID
FROM    INV_HEADERS h, VENDORS v
WHERE   h.LOOKUP_CODE in ('STANDARD', 'BLANKET')
AND     h.VENDOR_ID = v.VENDOR_ID
AND     h.FOLIO1 = @invNumber"

OracleCommand CMD = new OracleCommand();
OracleConnection CONN = new OracleConnection(constring.ConnectionString);

CMD.Connection = CONN;
CONN.Open();

CMD.Parameters.Clear();
CMD.Parameters.Add(new OracleParameter("@invNumber", INVNumber));
CMD.CommandText = getInfoByPoNum;

using (var reader = CMD.ExecuteReader())
{
    while (reader.Read())
    {  

The error occurs at CMD.ExecuteReader().
Based on other posts on SO and on the web, the query is correct and runs in oracle sql-developer.
What is causing the syntax error?

Update: If I modify the oracle query and enter a valid invoice number value instead of @invNumber, the query executes fine in my application.

getInfoByPoNum = 
    "SELECT h.SYS_HEADER_ID, 
        h.FOLIO1 AS INV_NUMBER, 
        v.VENDOR_NAME,
        CASE WHEN h.Comments LIKE '%CLOSED%' THEN 'CLOSED' ELSE NVL(h.Comments, 'OPEN') END AS CComments,
        h.ORG_ID
    FROM    INV_HEADERS h, VENDORS v
    WHERE   h.LOOKUP_CODE in ('STANDARD', 'BLANKET')
    AND     h.VENDOR_ID = v.VENDOR_ID
    AND     h.FOLIO1 = 2241QSA"

Answer 1

I believe that for Oracle your parameter should be specified as :invNumber, not @invNumber in your query:

AND     h.FOLIO1 = :invNumber"

And when setting your parameter, it should look like this (just remove the @):

CMD.Parameters.Add(new OracleParameter("invNumber", INVNumber));

EDIT

You may also need to enable parameter binding by name (I think it's positional by default):

CMD.BindByName = true;

Answer 2

Try putting all your query in the same line, it seems that only the first line of the string is being executed. Also check if there isn´t any escape character or special character that you have to treat with a "\" character.

Answer 3

And this may also occur, in my experience, when attempting to execute SQL with a terminating semicolon in the Oracle managed driver for .NET/C#.

So in that situation, execute the SQL within a wrapper for consistency and do not use

SELECT * FROM X;

use

SELECT * FROM X

in other words, strip it off.