Hmm … I must admit I know very little about Oracle and probably it won't change in the near future. Sometimes I have to deal with their products, in most cases in MIIS projects. This time, in my current assignment (which I hope I will manage to finish in next two weeks, and i hope that finish with success) I have to synchronize data with two sources (HR and financial system) based on Oracle database. In both cases we are using some views to read and update data in Oracle database. Views were prepared by customer's stuff and when I tried to use them I came across strange problem, which I'm strongly convinced lays in Oracle OLEDB driver (I'm using Oracle provider for .NET version 220.127.116.11).
In both cases situation was exactly the same, when I've tried to update view it ends with some error, in one case it was:
Check each OLE DB status value, if available. No work was done. ORA-04091: table is mutating, trigger/function may not see it ORA-06512: at "", line 73
and in Second:
0x80230808 ROW-00009: Cannot update row in a read-only rowsete
In both case the same command executed using sqlplus tool (which is command line client for Oracle) was sucessfull.
After some diagnostic traces (why Oracle is not giving with database tools to trace all queries performed on database like SQL Profiler for MS SQL does ???) probably I figured out the cause (but I'm not sure as I'm not Oracle guy). When OleDB driver is executing query it checkes schema of view and tables used in this queries – and after doing so it refuses to update it – probably some algorithm decides that this view is not updateable. But it can be updated as with sqlplus it works. I used workaround moving some attributes to multivalued table, and switching in one case from view to table but it doesn't resolve actual the problem. Does anybody came across such situation? Any solution? I've queried web search engines, and I asked somebody to query Oracle KB but with no luck.
One of my best friends (greetings for Michał) asked me for some more "Oracleish" posts, as he is Oracle developer. So here it is one for You Michał :).