Re: A pl/sql compiler bug?

this is normal behavior.

the select statement raises the exception only when the cursor is opened (i.e. the select is executed.) if the procedure had followed a logic path that had not caused the cursor to be opened, then the procedure would execute without raising the exception.

consider a case of a procedure that

1) creates a table (for use in the procedure)
2) populates the table
3) opens a cursor to select from the table, and
4) drops the table

if the select statement in the cursor were 'parsed' at compile time, the table it references may not exist, yet the programmer has written the procedure in such a way that the table does exist when the cursor is opened.

HTH. "bmlam" <bmlam_at_online.de> wrote in message news:38EF8234.86F1FDEE_at_online.de...
> Please consider the following script(s):
>
>
> create table test1 (
> region varchar2(10),
> month char(2),
> sale number
> );
>
> create or replace procedure test_proc1
> is
> cursor v_cur is
> select region, sum(sale),
> month
> from test1
> group by region
> ;
> begin
> for rec in v_cur loop
> null;
> end loop;
> end;
> /
>
> exec test_proc1;
>
>
> On my system, the script yields the following output:
>
> SQL> @test
>
> Table created.
>
>
> Procedure created.
>
> begin test_proc1; end;
>
> *
> ERROR at line 1:
> ORA-00979: not a GROUP BY expression
> ORA-06512: at "SYSTEM.TEST_PROC1", line 4
> ORA-06512: at "SYSTEM.TEST_PROC1", line 10
> ORA-06512: at line 1
>
>
> The point is: the SELECT statement in the cursor definition is
> straightly incorrect because MONTH should appear in the GROUP
 BY clause
> but it did not. And the compiler did not catch this!
 Eventually at run
> time, the error is detected but this is really annoying bcos
 you
> normally assumed that syntactical error in (static) SQL
 statements
> should have been ironed out once the procedure is compiled.
>
> Should I call this a PL/SQL compiler bug or am I missing
 something?
>
>
Received on Sat Apr 08 2000 - 00:00:00 CDT