Query works with Oracle 10g but not with 11g?

sql oracle plsql oracle11g ora-00979
Peter Lang picture Peter Lang · Dec 6, 2010 · Viewed 8.4k times · Source 
DECLARE
  trn  VARCHAR2(2) := 'DD';
  cur  SYS_REFCURSOR;
BEGIN
  OPEN cur FOR
    SELECT
      TRUNC(some_date, trn),
      NULL AS dummy_2,
      COUNT( DISTINCT dummy_1 )
    FROM
      (SELECT SYSDATE AS some_date, ROWNUM AS dummy_1 FROM dual)
    GROUP BY
      TRUNC(some_date, trn);
END;

This works with Oracle 10, but with Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production it results in:

ORA-00979: not a GROUP BY expression
ORA-06512: at line 5

Can anyone reproduce/explain this? Thanks!

Answer

Vincent Malgrat picture Vincent Malgrat · Dec 6, 2010

if you have access to support, it looks like Bug 9478304: LOOP FAILING WITH ORA-00979: NOT A GROUP BY EXPRESSION. This seems to affect 11.2.0.1 only.

Related questions

Using bind variables with dynamic SELECT INTO clause in PL/SQL

I have a question regarding where bind variables can be used in a dynamic SQL statement in PL/SQL. For example, I know that this is valid: CREATE OR REPLACE FUNCTION get_num_of_employees (p_loc VARCHAR2, p_job …

Read More
Sleep function in ORACLE

I need execute an SQL query in ORACLE it takes a certain amount of time. So I wrote this function: CREATE OR REPLACE FUNCTION MYSCHEMA.TEST_SLEEP ( TIME_ IN NUMBER ) RETURN INTEGER IS BEGIN DBMS_LOCK.sleep(seconds => TIME_); …

Read More
PLS-00428: an INTO clause is expected in this SELECT statement

I am wanting to store a Rownum as a variable rather than use a costly Join. I need to get this from a Select statement as the Rownum will be different on various environments so it cannot be a literal …

Read More