Friday, July 3, 2020

Script to re-compile all invalid custom objects in APPS schema (DBMS_DDL.ALTER_COMPILE & DBMS_UTILITY.COMPILE_SCHEMA)









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

  1. 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.
  2. 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;

0 Responses to “Script to re-compile all invalid custom objects in APPS schema (DBMS_DDL.ALTER_COMPILE & DBMS_UTILITY.COMPILE_SCHEMA)”

Post a Comment

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.