Cursor_sharing, mdsaora, ora-00937
January 8, 2007 1 Comment
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….