5 Replies Latest reply: Sep 23, 2012 8:30 AM by Lannie Liberty RSS

    Different results from SQL Developr vs ODP.Net app using same query

    TNCaver
      We added a new column to a database table, and some existing queries have been modified to use that column as a parameter in the where clause. When I test this modified query in SQL Developer, I get the expected results. But when I add that new parameter to the query in two different ASP.NET web sites, I get invalid results, as if it's not really passing the new parameter.

      Is there some query caching going on? If so, how can I clear it, or better yet, turn it off for this query? What else can I look at to fix this?
        • 1. Re: Different results from SQL Developr vs ODP.Net app using same query
          Alex.Keh .Product.Manager-Oracle
          Two possible reasons I can think of off the top of my head (though unlikely). You use DB Change Notification (aka Continuous Query Notification). This would exhibit the behavior you describe if you are not capturing a change that alters the schema on the client. However, I find this doubtful because using DCN is not trivial. You would know you were using it if you were the code developer.

          The other reason is that you are using edition based redefinition and you are still using the old edition. This is doubtful too if you are querying a table. Views are editionable, but not yet tables.

          If neither of these sound fruitful, try using the Oracle Developer Tools to perform the same query and see what results you get. ODT uses ODP.NET underlying it for data access.
          • 2. Different results from SQL Developr vs ODP.Net app using same query
            Lannie Liberty
            Verify the parameters occur in the same order as the BIND variables in the SQL statement or stored procedure.
            • 3. Re: Different results from SQL Developr vs ODP.Net app using same query
              Johnsung-Oracle
              I was thinking it was the BindByName Property too.
              Although you don't say what you mean by "invalid results" - an error ? Or are the results returned with wrong data?

              In any case if you have not set BindByName =true for your command object then it will default to position.
              See
              http://docs.oracle.com/cd/E11882_01/win.112/e23174/OracleCommandClass.htm#i997666

              If you want to use bind by position the parameters have to be in the right order as Eidercom says.

              John
              • 4. Re: Different results from SQL Developr vs ODP.Net app using same query
                TNCaver
                Thanks for all your efforts. After 5 hours of trying a million different things, I found the problem.

                It's wasn't the need for BindByName, as that's bitten me before, and I had that in place. And by invalid data, I mean the right results were not returned to the .Net app.

                I am very new to Oracle. I have 14 years of experience with MS SQL Server and DB2 databases. My Oracle training with PL/SQL was over a year ago, and I've used it very little since then, and only SELECT statements, not INSERTs or UPDATEs. But late in the day a co-worker reminded me that an update has to be followed by a Commit, which I was not doing. So, in SQL Developer I had changed a few records in a test account, then did my query to check that my where clause worked as expected, and of course, it did. But until the Commit was done, those changes were not visible outside that session, so the .Net app could not see those changes to the records. Once I did the Commit, of course it would see them and my query was validated.

                I will spare you a rant against Oracle about if I wanted to be able to roll back a change I'd explicitly use Begin Transaction and Commit/Rollback commands before and after the update, as it works in every other popular database out there like SQL Server, DB2, MySQL, etc. But since it wouldn't do any good, I'll spare you all. :-)

                Thanks again.
                • 5. Different results from SQL Developr vs ODP.Net app using same query
                  Lannie Liberty
                  I am "commit"-ted to using Oracle and have an awful time when I venture into MSSQL/MSACCESS/OLEDB world... nit picky differences. ODP.NET is fast. :-)