Re: A pl/sql compiler bug?

From: bmlam <bmlam_at_online.de>
Date: 2000/04/09
Message-ID: <38F05AAE.2E843169@online.de>#1/1

Spence, the answer you provided is I believe, incorrect. I just tested with the following script(s):

drop table test1;

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;

And here is the output:

SQL> @test

Table dropped.

Warning: Procedure created with compilation errors.

begin test_proc1; end;

      *
ERROR at line 1:

ORA-06550: line 1, column 7:
PLS-00905: object SYSTEM.TEST_PROC1 is invalid
ORA-06550: line 1, column 7:

PL/SQL: Statement ignored

SQL> sho err
Errors for PROCEDURE TEST_PROC1:

LINE/COL ERROR

---

---

4/3      PL/SQL: SQL Statement ignored
6/8      PLS-00201: identifier 'TEST1' must be declared

SQL> Note that Oracle has tagged the SELECT statement of the cursor at COMPILE time as invalid since the table had been dropped previously. And this is normal behaviour as I would expect all static SQL statements to be validated at compile time. Until I found the example the prompted this posting ...

spencer schrieb:

> 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 Sun Apr 09 2000 - 00:00:00 CDT