Skip to content

Generate Compile Invalid Objects (compile_invalid.gen)

What This Script Does

This generator script creates ALTER statements to compile invalid database objects. It handles different object types appropriately and orders them by dependency to minimize compilation errors.

The Script

rem  compile_invalid.gen
rem
select 'alter ' || decode(object_type,
       'PACKAGE BODY', 'package',
       'TYPE BODY', 'type',
       lower(object_type)) ||
       ' ' || owner || '.' || object_name || ' compile' ||
       decode(object_type,
       'PACKAGE BODY', ' body',
       'TYPE BODY', ' body',
       '') || ';'
  from sys.dba_objects
 where status = 'INVALID'
   and owner like upper('&owner')
   and object_type in ('PROCEDURE','FUNCTION','PACKAGE','PACKAGE BODY',
                       'TRIGGER','VIEW','TYPE','TYPE BODY')
 order by decode(object_type,
          'VIEW',1,
          'TYPE',2,
          'TYPE BODY',3,
          'PROCEDURE',4,
          'FUNCTION',5,
          'PACKAGE',6,
          'PACKAGE BODY',7,
          'TRIGGER',8,9),
          owner, object_name
/

Usage

-- Basic usage
@compile_invalid.gen

-- When prompted, enter:
-- owner: Schema owner pattern (use % for wildcard)

Parameters

The script prompts for:

&owner - Schema owner pattern (use % for wildcard)

Required Privileges

SELECT ANY DICTIONARY
-- OR --
SELECT ON DBA_OBJECTS
-- Plus compilation privileges on target objects

Sample Output

alter view HR.EMP_DETAILS_VIEW compile;
alter type HR.EMPLOYEE_TYPE compile;
alter type HR.EMPLOYEE_TYPE compile body;
alter procedure HR.ADD_JOB_HISTORY compile;
alter function HR.GET_EMPLOYEE_SALARY compile;
alter package HR.EMPLOYEES_PKG compile;
alter package HR.EMPLOYEES_PKG compile body;
alter trigger HR.UPDATE_JOB_HISTORY compile;

Key Features

Smart Ordering: Compiles objects in dependency order (views first, triggers last)
Body Handling: Correctly handles PACKAGE BODY and TYPE BODY syntax
Object Type Support: Covers all major compilable object types
Invalid Only: Only generates statements for currently invalid objects

Common Use Cases

Compile All Invalid Objects

@compile_invalid.gen
-- Enter: % for owner

Compile Invalid Objects in Specific Schema

@compile_invalid.gen
-- Enter: HR for owner

Invalid Objects Analysis - Find invalid objects
Object Analysis - Detailed object information