Tip

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

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.