Oracle Apps Knowledge Sharing

In this post, we have given a custom package while has below logic

Compiles all invalid custom objects (objects starting with XX%). This will be handy when every time a custom code deployment happens. This ensure all dependent objects are compiled and verified.

Compiles all objects in a schema

API used (invalid Objects only): DBMS_DDL.ALTER_COMPILE

API used (Complete Schema): DBMS_UTILITY.COMPILE_SCHEMA

Package:

CREATE OR REPLACE PACKAGE xxsh_dba_utilities
AUTHID CURRENT_USER
AS  
  PROCEDURE compile_invalid_apps_objects;
  PROCEDURE compile_schema (piv_schema_name IN VARCHAR2);
END xxsh_dba_utilities;
/
SHOW ERRORS;

CREATE OR REPLACE PACKAGE BODY xxsh_dba_utilities
AS
  -------------------------------------------------------------------
  -- PROCEDURE compile_invalid_apps_objects
  --------------------------------------------------------------------  
  PROCEDURE compile_invalid_apps_objects
  AS 
    CURSOR cur_invalid_objects 
    IS 
    SELECT object_name,
           object_type,
           owner
      FROM dba_objects 
     WHERE owner = 'APPS'
       AND status = 'INVALID'
       AND object_name like 'XX%'
       AND object_type IN ('PACKAGE',
                           'PACKAGE BODY',
                           'PROCEDURE',
                           'FUNCTION',
                           'TRIGGER',
                           'VIEW',
                           'MATERIALIZED VIEW',
                           'SYNONYM'
                           ); 
       
    lv_status  VARCHAR2(200);      
  BEGIN 
    FOR i IN cur_invalid_objects
    LOOP
      BEGIN
        dbms_output.put_line('Compiling Object named :'||i.object_name);   
        IF i.object_type IN ('VIEW','MATERIALIZED VIEW','SYNONYM')
        THEN 
          EXECUTE IMMEDIATE 
              'ALTER '||i.object_type ||' '||i.object_name||' COMPILE';
        ELSE        
        dbms_ddl.alter_compile(i.object_type,NULL,i.object_name);
        END IF;
        
        lv_status := NULL;
        SELECT status 
          INTO lv_status
          FROM dba_objects
         WHERE object_name = i.object_name
           AND object_type = i.object_type;
         
        dbms_output.put_line ('Post Compilation Object Status :'||lv_status);
        
      EXCEPTION 
        WHEN OTHERS THEN 
          dbms_output.put_line('Unhandled Exception:'||SQLERRM);
      END;
    END LOOP;
  END compile_invalid_apps_objects;

  -------------------------------------------------------------------
  -- PROCEDURE compile_schema
  --------------------------------------------------------------------    
  PROCEDURE compile_schema (piv_schema_name IN VARCHAR2)
  AS 
    ln_check  NUMBER;
  BEGIN 
    SELECT COUNT(1)
      INTO ln_check
      FROM dba_users 
     WHERE username = piv_schema_name;
  
    IF  ln_check > 0 
    THEN   
      dbms_utility.compile_schema(piv_schema_name);
      dbms_output.put_line('Schema named '
                           ||piv_schema_name
                           ||' compiled successfully'
                           );
    ELSE 
      dbms_output.put_line('Error: Schema named '
                            ||piv_schema_name
                            ||' does not exists'
                            );
    END IF;
  END compile_schema;

END xxsh_dba_utilities;
/
SHOW ERRORS;

Calling Script:

SET SERVEROUTPUT ON;
BEGIN 
  xxsh_dba_utilities.compile_invalid_apps_objects;
END;