Object compilation

Check for uncompiled or wrongly compiled objects

Execute this SQL command:

COLUMN object_name FORMAT A30
SELECT owner, object_type, object_name, status FROM dba_objects WHERE status='INVALID' ORDER BY owner, object_type, object_name;
wiki oracle object compilation

Manual compilation

Execute following SQL commands to manually compile:

alter package <PACKAGE NAME> compile;
alter package <PACKAGE NAME> compile body;
alter procedure <PROCEDURE NAME> compile;
alter function <FUNCTION NAME> compile;
alter trigger <TRIGGER NAME> compile;
alter view <VIEW NAME> compile;

Resolve issues during manual compilation

Find the compilation script

Example of SQL command which displays compilation script for view U_27819:

set long 10000
set pages 0
select dbms_metadata.get_ddl('VIEW','U_27819','SAPR3E') from dual;
wiki oracle object compilation

Check used objects

Check all objects in the compilation script for any defects or if they are present. You can use SQL DESCRIBE command:

describe SAPR3E.T705T;wiki oracle object compilation

*This example shows that table T505T which is part of view U_27819 does not exist and that is the reason of failed compilation

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.