RE: Invalid objects

I have a script which uses the dependencies to determine the order of recompilation. Have often used it, and almost always recompiles every invalid the first time around. If anyone is interested ...

I believe there is also a package :

execute dbms_utility.compile_schema('SCHEMA')

> -----Original Message-----
> From: Diana Duncan [SMTP:Diana_at_fileFRENZY.com]
> Sent: Thursday, October 19, 2000 12:35 AM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Invalid objects
>
> ...and, since there are often dependencies in objects that means you have
> to
> run the recompilation several times, I came up with the following little
> database procedure, which also handles views, procedures and functions...
>
> create or replace procedure recomp is
> cursor cnt_invalid_objects is
> select count(*)
> from user_objects
> where status = 'INVALID';
> cursor get_invalid_objects is
> select object_name, object_type
> from user_objects
> where status = 'INVALID';
> objRec get_invalid_objects%rowtype;
> cnt number := 0;
> iterations number := 0;
> sqlStr varchar2(500) := null;
> begin
> open cnt_invalid_objects;
> fetch cnt_invalid_objects into cnt;
> close cnt_invalid_objects;
> while (cnt > 0 and iterations < 5) loop
> for objRec in get_invalid_objects loop
> if (objRec.object_type = 'PACKAGE BODY') then
> sqlStr := 'alter PACKAGE ' ||
> objRec.object_name ||
> ' compile body';
> else
> sqlStr := 'alter ' || objRec.object_type ||
> ' ' ||
> objRec.object_name || ' compile';
> end if;
> execute immediate sqlStr;
> end loop;
> open cnt_invalid_objects;
> fetch cnt_invalid_objects into cnt;
> close cnt_invalid_objects;
> iterations := iterations + 1;
> end loop;
> end recomp;
> /
>
> Diana
>
> -----Original Message-----
> Sent: Wednesday, October 18, 2000 3:06 PM
> To: Multiple recipients of list ORACLE-L
>
>
>
> Ruth,
>
> I would have to modified your script a little because the way to compile
> the package and package body is different.
>
> it's
>
> select 'alter '||object_type||' '||owner||','||object_name||' compile;'
> from dba_objects
> where object_type not !='PACKAGE BODY' and status='INVALID'
> union all
> select 'alter package '||owner||'.'||object_name||' compile body;' from
> dba_objects
> where object_type='PACKAGE BODY' and status='INVALID';
>
> WInnie
>
>
>
>
>
> "Ruth Gramolini" <rgramolini_at_tax.state.vt.us>@fatcity.com on 10/18/2000
> 11:50:24 AM
>
> Please respond to ORACLE-L_at_fatcity.com
>
> Sent by: root_at_fatcity.com
>
>
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> cc:
>
>
>
> select 'alter '||object_type||' '||owner||'.'||object_name||' compile;'
> from
> dba_objects where status ='INVALID';
>
> Spool the output of this query ( or cut and paste) and run it. It will
> compile all invalid objects if there are no other problems.
>
> HTH,
> Ruth
> ----- Original Message -----
> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> Sent: Wednesday, October 18, 2000 1:55 PM
>
>
> >
> > How can you fix invalid stored procedures?
> >
> > -----
> > Sent using MailStart.com ( http://MailStart.Com/welcome.html )
> > The FREE way to access your mailbox via any web browser, anywhere!
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > --
> > Author:
> > INET: cemail_at_sprintmail.com
> >
> > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> > San Diego, California -- Public Internet access / Mailing Lists
> > --------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like subscribing).
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Ruth Gramolini
> INET: rgramolini_at_tax.state.vt.us
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author:
> INET: Winnie_Liu_at_infonet.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Diana Duncan
> INET: Diana_at_fileFRENZY.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
Received on Thu Oct 19 2000 - 04:01:11 CDT