SQL> create or replace procedure dawn_proc as
  2  d1 varchar2(10);
  3  d2 number;
  4  begin
  5  select 'sriram',y into d1,d2
  6  from table1
  7  group by y;
  8  end;
  9  /

Procedure created.

SQL> exec dawn_proc

PL/SQL procedure successfully completed.

SQL>  create or replace procedure dawn_proc as
  2   d1 number;
  3   d2 number;
  4   begin
  5   select x,y into d1,d2
  6   from table1
  7   group by x;
  8   end;
  9   /

Procedure created.

SQL> exec dawn_proc
BEGIN dawn_proc; END;

*
ERROR at line 1:
ORA-00979: not a GROUP BY expression
ORA-06512: at "SCOTT.DAWN_PROC", line 5
ORA-06512: at line 1


SQL> create or replace procedure dawn_proc as
  2  d1 varchar2(10);
  3  d2 number;
  4  begin
  5  select sriram,y into d1,d2
  6  from table1
  7  group by y;
  8  end;
  9  /

Warning: Procedure created with compilation errors.

SQL> sho err
Errors for PROCEDURE DAWN_PROC:

LINE/COL ERROR
-------- -----------------------------------------------------------------
5/1      PL/SQL: SQL Statement ignored
5/8      PL/SQL: ORA-00904: "SRIRAM": invalid identifier
SQL>

Hi Tom, 
In responding to Siram's question, you indicated 

"The first SQL you provided is syntactically 'correct', it isn't until they go to run it (which they do not do during compilation, they parse it - but they do not RUN it) that they discover "it isn't going to work" 

If I understand you properly, this is "syntactically 'correct'":

select dummy from dual
group by upper(dummy)


Hence I can compile:
CREATE OR REPLACE PROCEDURE P1 IS 
BEGIN
FOR L_ROW IN (
select dummy from dual
group by upper(dummy)
) 
LOOP NULL; END LOOP;
END; 

Though I can't say I understand fully WHY this is a runtime error that can only be detected at, well, runtime, I can at least accept that it is the case.  However, that leaves another mystery:  why do I get the ORA-00979 error when I attempt to Explain this query

select dummy from dual
group by upper(dummy)

The main way I ensure that I don't get ORA-00979 runtime errors in my programs is by pulling an explain plan of each individual query at compile time.  I guess my question comes down to: what is the difference between the parsing done for an explain plan and the parsing done by the PL/SQL compiler, such that the former can catch ORA-00979 errors but the latter cannot?


SQL> explain plan for select dummy from dual group by upper(dummy);
explain plan for select dummy from dual group by upper(dummy)
                        *
ERROR at line 1:
ORA-00979: not a GROUP BY expression

SQL> create or replace procedure kk_proc1 is
  2  begin  for l_row in 
  3  (select dummy from dual group by upper(dummy))
  4  loop null; end loop; end;
  5  /

Procedure created.