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

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


I suspect your problem is that the NULL in your SELECT needs to be in the GROUP BY even though it is a constant. I can't imagine why it would work in Oracle 10 but not 11, though.

Does it work if you remove NULL AS dummy_2?


This works without errors:

DECLARE  trn  VARCHAR2(2) := 'DD';  cur  SYS_REFCURSOR;BEGIN  OPEN cur FOR    SELECT      TRUNC(some_date, dtrn),      NULL AS dummy_2,      COUNT( DISTINCT dummy_1 )    FROM      (SELECT SYSDATE AS some_date, ROWNUM AS dummy_1 FROM dual) data1,      (SELECT trn AS dtrn FROM dual) data2    GROUP BY TRUNC(some_date, dtrn);END;

Problem is with trn variable and variable using in TRUNC function. Maybe it is bug.