Re: Recompile invalid prcedures -- help needed

From: Phil Preen <obiwan-preen_at_maytheforcebewithyou.co.uk>
Date: Wed, 5 Jan 2000 16:46:31 -0000
Message-ID: <947090954.430879@red.parallax.co.uk>

rem ***************************************************************
rem * file: comp_all.sql  created: 06/10/96    by: Robert Cordingley
rem * purpose: compile all database stored objects.
rem * to use:  log in using a the appropriate account then execute
rem *          this script using the following syntax:
rem *
rem *            SQL> @comp_all
rem *
rem * NOTE:    You should not have to run this script more than
rem *          once since it uses the order_object_by_dependency
rem *          table to compile objects in the proper order.  Any
rem *          compilation errors generated should be investigated.
rem ***************************************************************

set hea off pages 0 lines 79 verify off echo off

spool comp_all.tmp

select

    decode( OBJECT_TYPE, 'PACKAGE BODY',
'alter package ' || OBJECT_NAME || ' compile body;',
'alter ' || OBJECT_TYPE || ' ' || OBJECT_NAME || ' compile;' )
from

    user_objects a,
    sys.order_object_by_dependency b
where

    A.OBJECT_ID = B.OBJECT_ID(+) and
    STATUS = 'INVALID' and
    OBJECT_TYPE in ( 'PACKAGE BODY', 'PACKAGE', 'FUNCTION', 'PROCEDURE',

                      'TRIGGER', 'VIEW' )

order by

    DLEVEL DESC,
    OBJECT_TYPE,
    OBJECT_NAME; spool off

@comp_all.tmp Received on Wed Jan 05 2000 - 10:46:31 CST