A recent posting on SQL.RU asked why Oracle doesn’t raise such errors like “ORA-00979 not a group by expression” during PL/SQL compilation. Since I couldn’t find a link to the answer (though I read about it many years ago, but I don’t remember where…), I’ve decided to post short answer:
During PL/SQL compilation Oracle checks static SQL using only:
- Syntactic analysis – Oracle verifies that keywords, object names, operators, delimiters, and so on are placed correctly in your SQL statement. So such queries like “select * foRm dual” will fail during this validation. For example, we can get here such errors like:
ORA-00900: invalid SQL statement
ORA-00923: FROM keyword not found where expected
ORA-00924: missing BY keyword
ORA-00933: SQL command not properly ended
… - Semantic analysis – it verifies that references to host variables and database objects are valid(including their grants) and that host-variable datatypes are correct. For example, “select * from nonexisting_table” will fail this validation.
And since Oracle doesn’t validate all other types of errors during Syntactic and Semantic analysis, we can detect them only during optimization* or execution*.
For example, Oracle detects “ORA-00979 not a group by expression” during optimization phase.
NB. It doesn’t not apply to CREATE or ALTER VIEW, because Oracle executes optimization step for them. You can check it using trace 10053.
Simple example:
-- fORm instead of "from" - syntactic validation fails:
SQL> create table t(a int, b int, c int);
SQL> create or replace procedure p_syntactic is
2 cursor c is select a,b,sum(c) sum_c fORm t group by a;
3 begin
4 null;
5 end;
6 /
Warning: Procedure created with compilation errors.
SQL> sho error;
Errors for PROCEDURE P_SYNTACTIC:
LINE/COL ERROR
-------- -----------------------------------------------------------------
2/16 PL/SQL: SQL Statement ignored
2/40 PL/SQL: ORA-00923: FROM keyword not found where expected
-- semantic validation fails:
SQL> create or replace procedure p_semantic is
2 cursor c is select a,b,sum(blabla) sum_c from t group by a;
3 begin
4 null;
5 end;
6 /
Warning: Procedure created with compilation errors.
SQL> sho error;
Errors for PROCEDURE P_SEMANTIC:
LINE/COL ERROR
-------- -----------------------------------------------------------------
2/16 PL/SQL: SQL Statement ignored
2/31 PL/SQL: ORA-00904: "BLABLA": invalid identifier
-- As you can see this procedure passes successfully both syntactic and semantic analysis,
-- though query is not valid: it should raise "ORA-00979: not a GROUP BY expression"
SQL> create or replace procedure p_valid is
2 cursor c is select a,b,sum(c) sum_c from t group by a;
3 begin
4 null;
5 end;
6 /
SQL> sho error;
No errors.
-- Oracle checks such errors for "CREATE VIEW", because it runs optimization for the query text:
SQL> create view v_cursor as select a,b,sum(c) sum_c from t group by a order by a;
create view v_cursor as select a,b,sum(c) sum_c from t group by a order by a
*
ERROR at line 1:
ORA-00979: not a GROUP BY expression