0 Replies Latest reply: Aug 21, 2009 2:36 PM by 718678 RSS

    why recompiling a stored procedure would change returned data?

    718678
      We have a web ASP.net application and Oracle database. Everything was working fine for at least last 5 years and now we can't figure out why
      our stored procedures no longer work.

      Here is what huppening

      On our Oracle database if we do SELECT * FROM web_usertable we see every user who can ‘access’ the our system via website.

      user1 is me

      583 user1 null John q us 0 02 605
      670 user2 null Ed Black us 0 02 693

      Information about me and user2 is in the Oracle.

      The only difference is that I have been in the database for years and user2 is for about one month.

      When I go to the website and ‘enter’ ... Website is calling USP_GETUSERPROFILE that returns all my data. Everything works fine

      When user2 performs exactly the same task no data from Oracle is being returned. (Why NOT??? Data is in the Oracle Table)

      Order of things doesn’t make any difference: I can go 1st and everything would work ... then user2 and his data isn’t working OR he can go 1st and nothing being returned and I go 2nd and everything will work for me.


      Here is what we found and it is only one of many ways we can reproduce this problem:

      I go 1st and everything would work (We know that code and Stored Procedure is working) user2 goes 2nd and no data is returned (Something is wrong)(Why NOT??? Data is in the Oracle Table)

      Without closing any windows or restarting any application or IIS or any other actions … if at this point we simply drop and recreate USP_GETUSERPROFILE all user2 data immediately comes back.

      This issue is not only occurring with user2 data. It appears that any recently added user has this problem.

      It also isn’t limited to the USP_GETUSERPROFILE procedure we found other instances when recompiling a Stored Procedure solved a problem.

      We don't even know where to look. We also suspect that it might have someting to do with .NET because some other stored procedures in our applications being executed by C++ code don't have this problem.