Sunday, October 24, 2010

Fun with GROUP BY

What's wrong with this ?

(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.

  v_num integer;
  v_ret number;
  v_num := dbms_sql.open_cursor;
     '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);

But you can embed the SQL in a procedure and it compiles successfully.

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

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.

1 comment:

Laurent Schneider said...

indeed it remind me one issue

create table lsc_t (x varchar2(255), y date);
exception when others then
insert into lsc_t (x,y) values (sqlerrm, sysdate);

as sqlerrm is valid in plsql, the parser did not complain.


So the code above is doing nothing in 9i but is failing in 10g. So maybe your group by will fail in a next release ;-)