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
- What do you think about this tip? E-mail us at [email protected] 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.