Re: A pl/sql compiler bug?

Hi Arvind,

arvind schrieb:

> Hi bmlam
> The status of a proc/fn/pkg is determined by the validity of the objects it
> uses and it is determined at the creation time. In your case the object
> test1 does not exists and the procedure has an invalid status. However SQL
> statements are parsed only when you try to open the cursor.
>

That makes sense. It is however a pain in the neck: suppose you run a stored procedure that lasts for hours,
 before that you checked all sorts things, make sure you have enough storage, sufficient privileges, only to hit
an SQL statement with invalid syntax after 80% of the job was done. For arguments sake, I dont see a reason why such things can not be detected beforehand!

HTH
> Arvind Balaraman
>
> bmlam wrote in message <38F05AAE.2E843169_at_online.de>...
> >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 Mon Apr 10 2000 - 00:00:00 CDT