This discussion is archived
11 Replies Latest reply: Dec 20, 2012 12:55 AM by davidphiliplee RSS

ora-00936 with simple select statement

davidphiliplee Newbie
Currently Being Moderated
Hi,

I get "ORA-00936: missing expression" when I issue the following SQL:

select 4 - .2 from dual

from the following VB.NET code (3.5 framework):

Dim conn As Oracle.DataAccess.Client.OracleConnection = New Oracle.DataAccess.Client.OracleConnection(My.Settings.oraconn)
conn.Open()
Dim da As Oracle.DataAccess.Client.OracleDataAdapter
Dim ds As New DataSet
da = New Oracle.DataAccess.Client.OracleDataAdapter("select 4 - .2 from dual", conn)
da.Fill(ds)
MsgBox(ds.Tables(0).Rows(0)(0))

I have the following installed when connecting to 10.2.0.3 database:
11.2.0.1 client installed
11.2.0.3.20 ODP installed

This is really strange behaviour as the following SQL variants work:
select 4 - 0.2 from dual
select 4 - '.2' from dual
select -.2 + 4 from dual

This is a big problem as I have a complex application deployed in a number of sites so won't be able to rollout ODP 11

Any help would be greatly appreciated.

Thanks.
  • 1. Re: ora-00936 with simple select statement
    Tridus Journeyer
    Currently Being Moderated
    I don't have an Oracle 10 server to test against anymore and it seems to work in 11, so it seems like your best bet is going to be to just format the number to add the leading zero. As you said, "select 4 - 0.2 from dual" works.
  • 2. Re: ora-00936 with simple select statement
    636190 Explorer
    Currently Being Moderated
    Howdy,

    It doesn't work within my setup either. ODP.NET 11.20.30 32bit
    This does:
    OracleDataAdapter da = new OracleDataAdapter("select sum(4 - .2) from dual;", conn);

    I am only guessing but it appears that the parser does not like that first char to be a number....hope this works for you.
    r,
    dennis

    Edit: Gee ... this IS strange. I assume the issue is that you do not know beforehand what the "order" of the operands are. I tested a few other cases and sum() seems to clean it up.

    Edited by: djonio on Dec 19, 2012 7:32 AM
  • 3. Re: ora-00936 with simple select statement
    davidphiliplee Newbie
    Currently Being Moderated
    Thank you very much for the responses. Yes its 32bit client I'm working with.

    Unfortunately due to application design, there are millions of combinations of dynamic SQL that could be run, so it won't be practical to search through and edit each one. Also this is just a basic test case, there will be multiple records that would be rolled up with sum.
  • 4. Re: ora-00936 with simple select statement
    636190 Explorer
    Currently Being Moderated
    Ok ...
    Let me give it one more try .... I have no idea as to the precision and scale of your numbers but how about:
    string formatX = "{0:0##############.0###########}{1:0################.0#############}";
    string v = string.Format(formatX, 4, -.2);
    OracleDataAdapter da = new OracleDataAdapter("select " + v + " from dual", conn);

    It looks really, really gross but maybe it fits ...
    r,
    dennis
  • 5. Re: ora-00936 with simple select statement
    davidphiliplee Newbie
    Currently Being Moderated
    thanks Denis but unfortunately due to dynamic SQL there may be between 1 - 100 fields of varying datatypes
  • 6. Re: ora-00936 with simple select statement
    Tridus Journeyer
    Currently Being Moderated
    Alright, I don't get a couple of things here:

    1. If you're doing math with potentially hundreds of values, why are you going to the database at all? Your application can do this on its own just fine. If you were using values out of a table in the database (ie: select col2-col1 from table) it'd likely work.

    2. Does this work if you use parameters in the statement? "Dynamic SQL" without parameters is just asking for a SQL Injection attack.

    TBH, it sounds like you should rethink why you want to do this.
  • 7. Re: ora-00936 with simple select statement
    davidphiliplee Newbie
    Currently Being Moderated
    1: Its doing calculations with data from the database in complex ways. The SQL I provided is a basic example of the issue.

    2: No because the application is only using parameters where it is beneficial performance wise. Its not a web application and is used behind the company firewall so no SQL Injection

    This is the way the application works so no rethinking I'm afraid.
  • 8. Re: ora-00936 with simple select statement
    Tridus Journeyer
    Currently Being Moderated
    There's pleanty of SQl injection inside the company firewall, be it from corporate insiders intent on espionage/harm or malware designed to attack a company. The corporate firewall is a poor defense these days against those kinds of attacks.

    Plus, I suspect that parameters have a good chance to fix this issue.
  • 9. Re: ora-00936 with simple select statement
    davidphiliplee Newbie
    Currently Being Moderated
    I don't have a couple of million dollars free to have the application rewritten.

    If the corporate network has been infiltrated, SQL Injection is the last of my worries.
  • 10. Re: ora-00936 with simple select statement
    gdarling - oracle Expert
    Currently Being Moderated
    If this is a critical thing for you, I'd recommend you open a SR with support to get a bug filed on your behalf.

    Enabling a 10046 trace shows the following query is what the database ultimately tries to execute: select 4-. 2 from dual , which returns a 936 as expected (note the space between the . and the 2)

    Checking ODP and sqlnet traces, by the time it hit SQLNET it was changed, so I'd guess this to be an ODP parser bug.

    Hope it helps, but realize it probably doesn't.

    Greg
  • 11. Re: ora-00936 with simple select statement
    davidphiliplee Newbie
    Currently Being Moderated
    I was certain of a bug, and was hoping of a workaround outside of code modification such as a patch, or altering a session parameter. I might leave this question open for another day and close out and raise SR if having no luck.

    Greg, thank you very much you have been extremely helpful. The 100046 trace proves at that the issue is with ODP parsing.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points