Re: recompile invalid procedures/functions without Schema Manager

This will recompile invalid objects, but not necessarily in the correct sequence. Invalid objects must be recompiled in the inverse order of dependency. This cn be retrieved by the following query:

CREATE VIEW order_object_by_dependency

    AS

         SELECT      MAX(level) AS dlevel, object_id
           FROM      public_dependency
         CONNECT BY  object_id = PRIOR referenced_object_id
         GROUP BY    object_id

Then join to the invalid objects:

SELECT

         DECODE   (
                     object_type,
                     'PACKAGE BODY', 'ALTER PACKAGE ' || object_name
|| ' COMPILE BODY;',
                                    'ALTER ' || object_type || ' ' ||
object_name || ' COMPILE;'
                  )
  FROM
         user_objects                          user_objects,
         order_object_by_dependency        order_object_by_dependency
 WHERE
         user_objects.object_id        =
order_object_by_dependency.object_id(+)
   AND   status                        = 'INVALID'
   AND   object_type                   IN ( 'PACKAGE BODY', 'PACKAGE',

         dlevel desc,
         object_type,
         object_name

A far easier method is to use the supplied package:

DBMS_UTILITY.COMPILE_SCHEMA (schema VARCHAR2);

On Tue, 5 Jun 2001 12:38:20 +0200, "Andrzej Zareba" <anzareba_at_hotmail.com> wrote:

>Yuo can use this Oracle Headstart scripts in servermanager or sqlplus:
>
>set heading off
>set pagesize 0
>
>spool recompl.lst
>
>select 'alter '||decode(object_type, 'PACKAGE BODY', 'package',
>object_type)
> ||' '||object_name
> ||' compile'||decode(object_type, 'PACKAGE BODY', ' body;', ';')
>from user_objects
>where status = 'INVALID'
>order by object_type;
>
>spool off
>start recompl.lst
>
>Andrzej Zareba.
>
>> Michael Buresch
 

>> How can I recompile invalid procedures/functions without Schema Manager in
>> C++ or from the Servermanager?
>>
>
>
>
Received on Sat Jul 21 2001 - 23:49:24 CEST