1

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"
user990423
  • 1,397
  • 2
  • 12
  • 32
  • Can you give us C# code that can compile please? – sstan Aug 25 '15 at 13:33
  • Have you tried to change the syntax of passing the parameter to the query? Instead of @invNumber, send it as :invNumber – abhi Aug 25 '15 at 13:41
  • You should wrap your commands, connections, and readers into a using blocks as they implement `IDisposable`. See [this question](http://stackoverflow.com/questions/16985876/sqlconnection-sqlcommand-sqldatareader-idisposable) for an example. – mason Aug 25 '15 at 13:53

3 Answers3

4

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;
sstan
  • 35,425
  • 6
  • 48
  • 66
  • when I remove the @, I get an error "ORA-00904: "INVNUMBER": invalid identifier". I also included the BindByName, but no luck – user990423 Aug 25 '15 at 13:49
  • @user990423: It sounds like you didn't put the colon `:` in your query. You do need it there, but not when you instantiate the `OracleParameter` object. Look closely at the code I posted. – sstan Aug 25 '15 at 13:52
  • Adding the colon and removing the @ worked. Thanks @sstan – user990423 Aug 25 '15 at 14:04
  • I was missing the colon in my query. Thanks very much!! – Troy Aug 26 '15 at 01:51
  • The colon is Oracle-specific and as these folks have learned, required. Furthermote, the use of BindByName eliminates the need to worry about parameter order since the position of the parameters in the SQL does not need to match the order of the associated parameter collection. I never rely on the position. – Allen May 05 '22 at 08:58
0

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.

NicoRiff
  • 4,803
  • 3
  • 25
  • 54
0

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.

Allen
  • 546
  • 5
  • 12