Fun with GROUP BY

What's wrong with this ?

CREATE TABLE dog 
(id number, name varchar2(10), breed varchar2(10), birth_date date);

select name, breed, count(*)
from dog
group by name;
Yup, you get an ORA-0979 "Not a group by expression" triggered by the "breed" identifier in line 1.

If you execute it through DBMS_SQL...well, you can't. It actually fails at the parse phase.

declare
  v_num integer;
  v_ret number;
begin
  v_num := dbms_sql.open_cursor;
  dbms_sql.parse(v_num,
     'select name, breed, count(*) from dog group by name',dbms_sql.native);
  v_ret := dbms_sql.execute(v_num);
  dbms_sql.close_cursor (v_num);
end;
/
But you can embed the SQL in a procedure and it compiles successfully.

create or replace procedure get_dog is
begin
  for c_rec in (select name, breed, count(*) from dog group by name) loop
    null;
  end loop;
end;
/

Although it fails on execution, the compilation of the procedure has succeeded. It has worked out that the table and columns exist with appropriate privileges. With PLSQL_WARNINGS it will even do some datatype checking. But this demonstrates that the compiling a procedure does NOT do a full parse of the SQL statement. One reason, perhaps, for this is that parsing the SQL statement would mean it ending up in shared memory (eg visible through v$sql) and the compilation of a large package might have a big impact on shared resources.

The 'lite parse' performed by PL/SQL can be done using less shared resources and a reduced impact on other sessions. The drawback is that, you may occasionally get an SQL statement in your PL/SQL code that can never be successfully executed. However you should discover that quickly enough in your testing phase....

PS. Can anyone come up with other SQL 'parse' errors that are not caught when the statement is statically compiled into PL/SQL ? My 'test' would be that the statement fails during a DBMS_SQL.PARSE, but can be included (without recourse to dynamic SQL) in a procedure that can be compiled without error.