Cursor_sharing, mdsaora, ora-00937 part II

Previously, I mentioned an issue with the setting of the CURSOR_SHARING parameter to FORCE when combined with an application using a version of the MSDAORA driver.

This had resulted in an ORA-00937 error – “not a single-group group function”. The reason being that the driver was adding a ROWID column into the column list of a SELECT statement which did a GROUP BY. Not a nice side effect.

I have noted from my blog stats that there is a consistent trickle of people arriving at my blog that, given their search engine terms used to find me, seem to be having the same problem.

With this in mind, I can confirm that a recent change in the application to use the Oracle OLEDB driver has resolved this issue. However, I don’t unfortunately have visibility of the detailed changes made in the application to give any insight into this.

Cursor_sharing, mdsaora, ora-00937

On top of the Oracle database, most of my application experience has revolved around either Vignette or Java.

Therefore, when we had a problem the other day (in a test system, of course…) it was good to gain a little insight into some different drivers.

What did we do before Google? I can hardly remember.

Anyway, in order to try to boost the performance of part of the database, I thought I would sneak in an after logon trigger for a particular application that does not use bind variables. The trigger would fire for a particular connecting user and do an “ALTER SESSION” to set the “CURSOR_SHARING” parameter to “FORCE”.

Most of the application was fine. However, a small legacy bit of code was raising an Oracle error from within the VB layer:

“ORA-00937: not a single-group group function.”

From metalink (although it wasn’t an immediately obvious search to find it there) and from a single entry on google groups, it turns out that there is an issue with the microsoft mdsadora driver. The metalink article talked also talked about OCI 7 and OCI 8.

Apparently and allegedly, if the cursor_sharing parameter is set to anything other than “EXACT”, then the driver appends a column into the select clause such that something like “SELECT count(*) FROM table” becomes “SELECT count(*), ROWID from table” … and the inevitable error arises. So, no CURSOR_SHARING setting then. Damn….