Re: Complie Oracle Stored Procedures

From: Gints Plivna <gints.plivna_at_gmail.com>
Date: 21 Sep 2006 13:11:34 -0700
Message-ID: <1158869494.560085.205190_at_d34g2000cwd.googlegroups.com>

Frank van Bortel wrote:
> Gints Plivna schreef:
>
> > At least in 9i there is dbms_utitlity.compile_schema. It can compile
> > only one schema in one call, but this shouldn't be a big problem.
> >
>
> Yes - but it fails to compile views, which may cause procedures
> to remain invalid
>
> --
> Regards,
> Frank van Bortel
>
> Top-posting is one way to shut me up...

You are partially right :)

It really fails to compile views directly, but compile procedural objects and also views if these object are based on views as you can see below:
SQL> select * from v$version;

BANNER

---

Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production PL/SQL Release 9.2.0.4.0 - Production
CORE 9.2.0.3.0 Production
TNS for Linux: Version 9.2.0.4.0 - Production NLSRTL Version 9.2.0.4.0 - Production

SQL> create table a (a number);

Table created.

SQL> create view v as select * from a;

View created.

SQL> create view v1 as select * from a;

View created.
SQL> ed
Wrote file afiedt.buf

  1 create or replace procedure p as
  2 v number;
  3 begin
  4 select a into v from v;
  5* end;
SQL> / Procedure created.

SQL> desc v

 Name                                      Null?    Type
 ----------------------------------------- -------- --------
 A                                                  NUMBER

SQL> desc v1
 Name                                      Null?    Type
 ----------------------------------------- -------- --------
 A                                                  NUMBER

SQL> alter table a add (b number);

Table altered.

SQL> desc v
ERROR:
ORA-24372: invalid object for describe

SQL> desc v1
ERROR:
ORA-24372: invalid object for describe

SQL> exec dbms_utility.compile_schema(user);

PL/SQL procedure successfully completed.

SQL> desc v

 Name                                      Null?    Type
 ----------------------------------------- -------- --------
 A                                                  NUMBER

SQL> desc v1
ERROR:
ORA-24372: invalid object for describe

Anyway thanks, never knew that it fails to compile views.

Gints Plivna
http://www.gplivna.eu Received on Thu Sep 21 2006 - 22:11:34 CEST