Wednesday, February 29, 2012

Oracle ODBC issue - AccessViolationException: Attempted to read or write protected memory. This is often an indication that other memory is corrupt.

In one of the projects I am working on (the application is a hybrid between .NET and COM/C++) the Oracle ODBC driver was used (in the C++ part of the application) - don't ask me why. :)
The client company wanted to migrate the database from Oracle 10g to Oracle 11g and the C++ part was failing with the exception from the title of the post when doing a SELECT * from SomeView where condition.
On the web there are a lot of possible solutions but none of them worked. The strange part is that the exception was only handled in .NET environment - when debugging the C++ part, I've noticed that the GetData  from afxdb.h was failing. So, I enabled tracing in the ODBC Data sources (Control  panel -> Admin tools) and re-execute the app. In this log I was able to see an ORA-01445 error which does not tell much (since the view was not using ROWID). I asked an Oracle DBA what's the error about and he asked me for the view code.
The view code was built using INNER JOINS. So, he told me to remove the INNER JOINS and make a simple SELECT clause: select table1.col1, table2.col2, table3.col2, from table1, table2, table3 where table1.col1 = table2.col1 and table1.col1 = table3.col1. And the application started to do it's job! Strange, right?

Some questions still remain (even if my issue is partially solved):
- why was it working on 10g?
- why this particular view crashed? (there are other views that use inner join and don't cause this)
- why was it working when using the full select (with inner join) and not working when selecting over the view?

Feel free to comment on this issue. ;)

1 comment:

Catalin Spoiala said...

Although both statements should return the same results, it is however the order in which INNER JOIN and WHERE clauses join tables that makes the difference. When using WHERE clause you basically perform an EQUIJOIN or possibly an OUTER JOIN on the same tables, yet the order in which this happens is different than in the case of INNER JOIN. This can cause glitches in the SQL execution plan and potentially increase SQL cost. I assume the DBA noticed this and recommended you use WHERE instead of INNER JOIN.

Te salut,