Thursday, December 13, 2007

How to Compile Invalid objects in a schema in Oracle

CREATE OR REPLACE PROCEDURE COMPILE_INVALID
IS
BEGIN
DECLARE
CURSOR COMPILE_INV IS
SELECT OBJECT_NAME,OBJECT_TYPE FROM USER_OBJECTS
WHERE STATUS='INVALID' AND OBJECT_TYPE IN
('PROCEDURE','FUNCTION','TRIGGER','PACKAGE','PACKAGE BODY');
US_N VARCHAR2(30);
BEGIN
SELECT USER INTO US_N FROM DUAL;
FOR C_ROWS IN COMPILE_INV
LOOP
DBMS_DDL.ALTER_COMPILE(C_ROWS.OBJECT_TYPE,US_N,C_ROWS.OBJECT_NAME);
END LOOP;
END;
END;
/

No comments: