My Oracle Support Banner

ORA-979 Reported By SQL Tuning Advisor For Query With Both Bind and CASE WHEN on GROUP BY If cursor_sharing=force (Doc ID 2600419.1)

Last updated on JANUARY 23, 2020

Applies to:

Oracle Database - Enterprise Edition - Version 12.2.0.1 and later
Information in this document applies to any platform.

Symptoms

ORA-979 is reported by SQL Tuning Advisor for following query which is executed with cursor_sharing=force by a user other than SYS.

Sample SQLs:

select case dummy when 'a' then 'a' else dummy end from dual
group by case dummy when 'a' then 'a' else dummy end;



or


select decode(dummy,'a', 'a' , dummy ) from dual group by
            decode(dummy,'a', 'a' , dummy );


Test log:

SQL> conn t108/t108
Connected.
SQL> alter session set cursor_sharing = force;

Session altered.

SQL> DECLARE
my_task_name VARCHAR2(30);
  2    3  my_sqltext CLOB;
  4  BEGIN
  5  my_sqltext := 'select case dummy when ''a'' then ''a'' else dummy end from dual group by case dummy when ''a'' then ''a'' else dummy end ';

  6    7  my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
                        sql_text => my_sqltext,
                        task_name => 'MY323');
END;
/  8    9   10   11

PL/SQL procedure successfully completed.

SQL> Execute dbms_sqltune.Execute_tuning_task (task_name => 'MY323');

PL/SQL procedure successfully completed.

SQL> set long 65536
set longchunksize 65536
set linesize 100
select dbms_sqltune.report_tuning_task('MY323') from dual;
SQL> SQL> SQL>
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY323')
----------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : MY323
Tuning Task Owner  : T108
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status  : COMPLETED
Started at         : 10/15/2019 23:01:09
Completed at       : 10/15/2019 23:01:10


DBMS_SQLTUNE.REPORT_TUNING_TASK('MY323')
----------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Schema Name: T108
SQL ID     : djg5uyzs85ygf
SQL Text   : select case dummy when 'a' then 'a' else dummy end from dual
             group by case dummy when 'a' then 'a' else dummy end

-------------------------------------------------------------------------------
ERRORS SECTION
-------------------------------------------------------------------------------
- ORA-00979: not a GROUP BY expression


DBMS_SQLTUNE.REPORT_TUNING_TASK('MY323')
----------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------


SQL>




Changes

 Set cursor_sharing=force.

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Changes
Cause
Solution


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.