Compiling object dependencies

Keep track of your Oracle database object changes with this script that automates the process.

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


This was first published in May 2001

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close