ORA-979:Not A Group By Expression With CURSOR_SHARING=Similar Or Force

OS： HP-UX 11.31
ORACLE_DATABASE：11.2.0.2.0

执行SQL语句时报错


OMS资料（ Doc ID 1081548.1）：
Applies to:
Oracle Server - Enterprise Edition - Version: 11.1.0.7 and later   [Release: 11.1 and later ]
Information in this document applies to any platform.
Symptoms
ORA-979: not a GROUP BY expression with CURSOR_SHARING=similar or force.

With CURSOR_SHARING=exact, the query works.

Errorstack is similar to

 kgesev
The failing query has GROUP BY clause which contains expressions involving literals,
i.e.
group by decode(conv2.fndiatarif, 0, to_date(tari2.dat_movimento,'DD/MM/YY'), to_date('01/'||to_char(tari2.dat_movimento, 'MM/YY'),'DD/MM/YY'))

       
Changes
Cursor_sharing parameter set to similar or force
Cause
Error is caused by unpublished Bug 8913729 which will be fixed in 12.1 :
Abstract: ORA-979: Not A Group By Expression With Cursor_Sharing=Similar Or Force


解决办法：
1、设置CURSOR_SHARING参数为exact
2、在SQL语句中使用hint：
   SELECT /*+ CURSOR_SHARING_EXACT */ ......
3、设置optimizer_features_enable参数为10.2.0.5或者11.1.0.7

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26194851/viewspace-746482/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26194851/viewspace-746482/