We have come across a bug that is referenced in numerous places throughout the Oracle Community Forums and stack overflow, but no one from Oracle has answered it. Because the forum posts are archived I cannot reply to them, so I am creating this new thread.
Basically we have an application using Entity Framework 5 and Oracle.ManagedDataAccess.dll v4.121.2.0 that randomly will generate bad SQL to send to Oracle. This bad SQL then gets cached by Entity Framework's Query Plan cache and the application will continuously send the bad SQL to the database. The only way to fix the problem is then to completely restart the application and clear the cache. User Greg Bachraty seems to have figured out the issue, but no one has replied to his requests. It seems to be an issue with a static variable ("top_s") being shared across threads that is corrupting the generated SQL. The code in question is located in "SqlGen.SqlSelectStatement". It looks like it affects Oracle.DataAccess, Oracle.ManagedDataAccess, and Oracle.ManagedDataAccess.EntityFramework. We have been unable to reproduce the issue on dev as of yet, but it seems to be with the same type of queries, ones that cause the Oracle data provider to use a TopClause in its SelectStatement query generation. The error in question is "ORA-00933: SQL command not properly ended".
Here are a list of links all referring to the same issue ranging from back in 2014 all the way to the present (2017).
Race condition in ODAC causes ORA-00933 command text corruption
Mysterious ORA-00933 on startup when using ODAC unmanaged/managed
https://stackoverflow.com/questions/43554591/sometimes-error-ora-00933-occurs-when-using-entity-framework-to-query-to-or…
https://stackoverflow.com/questions/38612941/mysterious-ora-00933-when-i-create-two-parallel-requests
Have there been any updates on this? A lot of people are experiencing the issue, and it is very difficult to deal with. Luckily Greg Bachraty has done a lot of good research to help this get off the ground, but it seems that no one has picked it up.
Until the code is fixed, Greg mentions this as the best workaround.
"Best workaround I found is to manually serialize query generation via global locking which is quite prohibitive in high-throughput scenarios."
How does one go about doing this in Entity Framework?
***Moderator action (Timo): removed call out to other member(s) who are not participating on the thread already.***