11 Replies Latest reply: Dec 20, 2012 2:55 AM by davidphiliplee RSS

    ora-00936 with simple select statement


      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)
      Dim da As Oracle.DataAccess.Client.OracleDataAdapter
      Dim ds As New DataSet
      da = New Oracle.DataAccess.Client.OracleDataAdapter("select 4 - .2 from dual", conn)

      I have the following installed when connecting to database: client installed 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.

        • 1. Re: ora-00936 with simple select statement
          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

            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.

            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
              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
                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 ...
                • 5. Re: ora-00936 with simple select statement
                  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
                    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
                      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
                        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
                          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
                            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.

                            • 11. Re: ora-00936 with simple select statement
                              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.