ORA-00937 when CURSOR_SHARING=FORCE

fmons@ayto-santander.es · *nix forums beginner Joined: 21 Dec 2005 Posts: 4

Hello,

I have read about the use of bind variables and how dramatically it can
inprove the performance on the Database servers.

Unafortunatelly I have several applications running against my Oracle
servers that doesn't make good use of this feature. In most of the
cases they simply don't work with bind variables at all.

Afortunatelly, Oracle has a init parameter that considers this
problematic, and internally manages all the similar SQLs implementing
on them bind variables.

This parameter is to set Cursor_Sharing=Force

My problem is that when i set this parameter some SQLs that before the
change where working, now they don't. The error that Oracle is raisng
is the ORA-00937: not a single-group group function.

One of the SQL's that is raising the error is the following:

Select Count(*) as Total From IINEHABIT Where
IgCargasM='00000CRGM001000176'

Thanks in advance.

Regards,
Fernando Mons.

fmons@ayto-santander.es · *nix forums beginner Joined: 21 Dec 2005 Posts: 4

Sorry,

I forgot some details about the problem:

1. I'm using Oracle 9.2.0.1.0
2. The errors dissapear when I return this parameter to the EXACT
value.
3. The error doesn't repoduce in sql*plus, only takles place in the
applications.

I hope you can help with this issue.

Thanks.

sybrandb@yahoo.com · *nix forums Guru Joined: 30 May 2005 Posts: 482

You can several things
- search for this error on Metalink. As I have the feeling it is a bug
you'll need
- to upgrade to 9.2.0.6.0 at least
Alternatively you could trace the affected session and search for
err=937
in your trace file, and watch the *exact* statement resulting in this
error.
You could also do the following (in sql*plus)
variable x varchar2(...)
begin
Mad

:= '<your value>';
end;
/
Select Count(*) as Total From IINEHABIT Where
IgCargasM= Mad

/
To verify whether this behavior occurs with a *true* bind variable
(sql*plus doesn't use bind variables by default, so it MUST use the
same mechanism as in the affected program)

Hth

--
Sybrand Bakker
Senior Oracle DBA

fmons@ayto-santander.es · *nix forums beginner Joined: 21 Dec 2005 Posts: 4

Hi Sybrand,

Thanks for your fast info.

I have some doubts about your answers:

You said:

"Alternatively you could trace the affected session and search for
err=937
in your trace file"

And I say:

I didn't know that tracing application SQLs with Oracle was
possible!!!! Can you give me some information about tracing or a link
to read about it??

I also have searched on metalink as a first resource but nothing found,
so I come to this forum by recomdation of Aktom.com

sybrandb@yahoo.com · *nix forums Guru Joined: 30 May 2005 Posts: 482

Hi Fernando

Amongst others

http://download-uk.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_23a.htm#2141496

to set trace for the complete instance.
You could use dbms_system.set_sql_trace_in_session(sid, serial#,
true|false) to enable trace for a specific session (documented on
http://tahiti.oracle.com)

You could also dump the errorstack for error 937 (to see the exact
statement in error), but the syntax varies by error.
Further details usually on Metalink.
The command is something like
alter system set events="937 trace name errorstack, level 1"

And then of course you could create an after logon trigger on the
affected schema, to make sure there is an
execute immediate 'alter session set sql_trace=true' run automatically.

Hth
--
Sybrand Bakker
Senior Oracle DBA

bdbafh@gmail.com · *nix forums Guru Joined: 18 Jul 2005 Posts: 322

One item that deserves mentioning, is that hinting the problematic
statement with

/*+ cursor_sharing_exact */

should take care of this issue, without requiring that
cursor_sharing=exact be set session-wide.

I've seen this type of issue before on 10.1.0.3 on win32. It was fixed
in 10.1.0.4.

IMHO, base releases of the product really aren't for production use -
they're for testing.
The 9i R2 release wasn't "ready" until 9.2.0.4.

If you have metalink access check the readme.html for the 9.2.0.7
patchset and you'll likely see fixes listed for this class of error.

hth.
-bdbafh

fmons@ayto-santander.es · *nix forums beginner Joined: 21 Dec 2005 Posts: 4

I am going to upgrade my server to Patchset 9.2.0.7, to try to solve
it. Until then I will not use this parameter with the FORCE value.

Thanks for your info. It was very helpful for me.

Regars,
F.Mons

ricardao · *nix forums beginner Joined: 26 Sep 2007 Posts: 2

You can set CURSOR_SHARING to SIMILAR or FORCE

But, for SQL statments that use group clauses (like SUM, COUNT) you need to set some parameters in your ado connection (MSDAORA driver) to avoid this problem:

LockType = adLockReadOnly
CursorType = adOpenForwardOnly

ricardao · *nix forums beginner Joined: 26 Sep 2007 Posts: 2

information...

When the CURSOR_SJARING is set to SIMILAR or FORCE, MSDAORA driver detect and modify your SQL statement, like this:

Original:
SELECT COUNT(*) FROM some_table;

Modified by driver:

SELECT COUNT(*), some_table.rowid FROM some_table;

this cause the error ORA-00937, becouse of the lack of GROUP BY at the end of statement.

Google