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. ;)