Compiling object dependencies

Have you ever made a change to an Oracle database object, requiring you to recompile all objects that reference...

the modified object? If so, did you have any trouble coming up with the exact syntax to manually compile the various referencing objects? When you did compile those objects, you may have encountered errors while compiling and wondered whether these objects had errors before you even started re-compiling.

Below is a script to help ease this procedure. This script will automatically generate the DDL to compile objects that reference the specified object; plus it shows the status (VALID or INVALID) of the referencing object BEFORE the re-compile is done. Additionally, the script logs all of this status information and the output of the commands in a log file automatically for your permanent records.

SET PAGESIZE 2000
SET LINESIZE 132
SET VERIFY OFF
SET FEEDBACK OFF
SET HEADING OFF
CLEAR COLUMNS
---
PROMPT
PROMPT   This is designed to produce a script that will compile all objects
PROMPT   that reference (i.e., use) the specified object.
PROMPT
---
ACCEPT p_owner PROMPT 'Object owner: '
ACCEPT p_object PROMPT 'Object name: '
PROMPT
---
SET TERMOUT OFF
spool COMP_&p_object..sql
prompt spool COMP_&p_object..log
prompt set feedback off
prompt select to_char(sysdate,'fmDay, dd Month, yyyy fmhh24:mi') as "Current Date" from dual
prompt /
prompt set feedback on
prompt prompt
prompt set echo on
prompt set time on
---
SET TERMOUT ON
PROMPT --- Working... (6 left)
SET TERMOUT OFF
--
--       Compile FUNCTIONs:
--
select 'alter ' || o.object_type || ' ' || o.owner || '.' || o.object_name || 
       ' compile /* previously ' || o.status || ' */ ;'
from 
   dba_objects o
where 
    o.object_type      = 'FUNCTION'            and
    o.owner            = upper ('&p_owner')    and
    o.object_name      = upper ('&p_object')
/

select 'alter ' || d.type || ' ' || d.owner || '.' || d.name || 
       ' compile /* previously ' || o.status || ' */ ;'
from 
   dba_dependencies d, dba_objects o
where 
    d.type             = 'FUNCTION'            and
    d.referenced_owner = upper ('&p_owner')    and
    d.referenced_name  = upper ('&p_object')   and
    o.object_type      = d.type                and
    o.owner            = d.owner               and
    o.object_name      = d.name
/

SET TERMOUT ON
PROMPT --- Working... (5 left)
SET TERMOUT OFF
--
--       Compile PACKAGE BODYs:
--
select 'alter package ' || o.owner || '.' || o.object_name || 
       ' compile body /* previously ' || o.status || ' */ ;'
from 
   dba_objects o
where 
    o.object_type      = 'PACKAGE BODY'        and
    o.owner            = upper ('&p_owner')    and
    o.object_name      = upper ('&p_object')
/

select 'alter package ' || d.owner || '.' || d.name || 
       ' compile body /* previously ' || o.status || ' */ ;'
from 
   dba_dependencies d, dba_objects o
where 
    d.type             = 'PACKAGE BODY'        and
    d.referenced_owner = upper ('&p_owner')    and
    d.referenced_name  = upper ('&p_object')   and
    o.object_type      = d.type                and
    o.owner            = d.owner               and
    o.object_name      = d.name
/

SET TERMOUT ON
PROMPT --- Working... (4 left)
SET TERMOUT OFF
--
--       Compile PACKAGEs:
--
select 'alter package ' || o.owner || '.' || o.object_name || 
       ' compile package /* previously ' || o.status || ' */ ;'
from 
   dba_objects o
where 
    o.object_type      = 'PACKAGE'             and
    o.owner            = upper ('&p_owner')    and
    o.object_name      = upper ('&p_object')
/

select 'alter package ' || d.owner || '.' || d.name || 
       ' compile package /* previously ' || o.status || ' */ ;'
from 
   dba_dependencies d, dba_objects o
where 
    d.type             = 'PACKAGE'             and
    d.referenced_owner = upper ('&p_owner')    and
    d.referenced_name  = upper ('&p_object')   and
    o.object_type      = d.type                and
    o.owner            = d.owner               and
    o.object_name      = d.name
/

SET TERMOUT ON
PROMPT --- Working... (3 left)
SET TERMOUT OFF
--
--       Compile PROCEDUREs:
--
select 'alter ' || o.object_type || ' ' || o.owner || '.' || o.object_name || 
       ' compile /* previously ' || o.status || ' */ ;'
from 
   dba_objects o
where 
    o.object_type      = 'PROCEDURE'           and
    o.owner            = upper ('&p_owner')    and
    o.object_name      = upper ('&p_object')
/

select 'alter ' || d.type || ' ' || d.owner || '.' || d.name || 
       ' compile /* previously ' || o.status || ' */ ;'
from 
   dba_dependencies d, dba_objects o
where 
    d.type             = 'PROCEDURE'           and
    d.referenced_owner = upper ('&p_owner')    and
    d.referenced_name  = upper ('&p_object')   and
    o.object_type      = d.type                and
    o.owner            = d.owner               and
    o.object_name      = d.name
/

SET TERMOUT ON
PROMPT --- Working... (2 left)
SET TERMOUT OFF
--
--       Compile TRIGGERs:
--
select 'alter ' || o.object_type || ' ' || o.owner || '.' || o.object_name || 
       ' compile /* previously ' || o.status || ' */ ;'
from 
   dba_objects o
where 
    o.object_type      = 'TRIGGER'             and
    o.owner            = upper ('&p_owner')    and
    o.object_name      = upper ('&p_object')
/

select 'alter ' || d.type || ' ' || d.owner || '.' || d.name || 
       ' compile /* previously ' || o.status || ' */ ;'
from 
   dba_dependencies d, dba_objects o
where 
    d.type             = 'TRIGGER'             and
    d.referenced_owner = upper ('&p_owner')    and
    d.referenced_name  = upper ('&p_object')   and
    o.object_type      = d.type                and
    o.owner            = d.owner               and
    o.object_name      = d.name
/

SET TERMOUT ON
PROMPT --- Working... (1 left)
SET TERMOUT OFF
--
--       Compile VIEWs:
--
select 'alter ' || o.object_type || ' ' || o.owner || '.' || o.object_name || 
       ' compile /* previously ' || o.status || ' */ ;'
from 
   dba_objects o
where 
    o.object_type      = 'VIEW'                and
    o.owner            = upper ('&p_owner')    and
    o.object_name      = upper ('&p_object')
/

select 'alter ' || d.type || ' ' || d.owner || '.' || d.name || 
       ' compile /* previously ' || o.status || ' */ ;'
from 
   dba_dependencies d, dba_objects o
where 
    d.type             = 'VIEW'                and
    d.referenced_owner = upper ('&p_owner')    and
    d.referenced_name  = upper ('&p_object')   and
    o.object_type      = d.type                and
    o.owner            = d.owner               and
    o.object_name      = d.name
/

---
---
---
CLEAR COLUMNS
PROMPT SET TIME OFF
PROMPT SET ECHO OFF
PROMPT PROMPT The log file is named:  COMP_&p_object..log
PROMPT SPOOL OFF
SPOOL OFF
SET TERMOUT ON
PROMPT
PROMPT The script is named:  COMP_&p_object..sql
SET VERIFY ON
SET FEEDBACK ON
SET HEADING ON

For More Information

• What do you think about this tip? E-mail us at editor@searchDatabase.com with your feedback.
• The Best Oracle Web Links: tips, tutorials, scripts, and more.
• Have an Oracle tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
• Ask your technical Oracle questions--or help out your peers by answering them--in our live discussion forums.
• Ask the Experts yourself: Our Oracle gurus are waiting to answer your toughest questions.