Manual Script to compile invalid objects in Oracle

We have many methods to compile invalid objects in oracle, most of the provided methods are for bulk recompiling of the objects. Some times we may have invalid objects distributed in several schema but not all of the objects. In such cases we may have to write manual script to validate/ compile them.

Below is the script to compile all invalid objects which includes PACKAGE, PACKAGE BODY, PROCEDURE, MATERIALIZED VIEW, SYNONYM, VIEW, FUNCTION and TRIGGER.

SET SERVEROUTPUT ON SIZE 1000000
BEGIN
  FOR cur_invobj IN (SELECT owner,
                         object_name,
                         object_type
                  FROM   dba_objects
                  WHERE  status = 'INVALID'
                  AND    object_type in ('PACKAGE','PACKAGE BODY','PROCEDURE','MATERIALIZED VIEW','SYNONYM','VIEW','FUNCTION','TRIGGER')
                  ORDER BY 3)
  LOOP
    BEGIN
      IF cur_invobj.object_type = 'PACKAGE BODY' THEN
        EXECUTE IMMEDIATE 'ALTER PACKAGE "' || cur_invobj.owner ||'"."' || cur_invobj.object_name || '" COMPILE BODY';
      ElSE
        EXECUTE IMMEDIATE 'ALTER ' || cur_invobj.object_type || ' "' || cur_invobj.owner || '"."' || cur_invobj.object_name || '" COMPILE';
      END IF;
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.put_line('Not Compiled: '|| cur_invobj.object_type || ' : ' || cur_invobj.owner || '.' || cur_invobj.object_name);
    END;
  END LOOP;
END;
/

At the end you will get the objects which cannot be compiled. It is not possible to compile an object if there is an error in the code. The actual errors will not be listed while compiling them.
Read: How to find compilation errors related to specific object in oracle

Note:

For invalid synonyms, a select from the synonym will compile them.
Example:
SELECT count (1) FROM my_synonym;

The syntax to compile invalid objects except package body is 
ALTER <OBJECT_TYPE> [<SCHEMA_NAME>].<OBJECT_NAME> COMPILE;

To compile package body, the syntax is,
ALTER PACKAGE [<PACKAGE_OWNER>].<PACKAGE_NAME> COMPILE BODY;

No comments:

Post a Comment