后台编译所有存储过程和包

rem-- Realised by Patch-Adams
rem-- Email: robert.thibault@ssss.gouv.qc.ca
rem--
rem-- This script compile ('FUNCTION','PROCEDURE','VIEW','PACKAGE','PACKAGE BODY','TRIGGER') with status is invalid
rem-- You can call this script with a task scheduler for compile every day if you want
rem--
rem-- How use it
rem-- You can run this script Oracle 7.3,8,8i,9i
rem-- Run this script on the user "owner" with SQLPLUSW.EXE

DROP PACKAGE PACK_ALL_COMPILE;

CREATE OR REPLACE PACKAGE PACK_ALL_COMPILE IS

PROCEDURE PROC_CALL_ALL;

END;
/

CREATE OR REPLACE PACKAGE BODY PACK_ALL_COMPILE IS

PROCEDURE EXECUTE_STRING(T_STRING IN VARCHAR2) AS

V_CURSOR INTEGER;
V_DUMMY INTEGER;

BEGIN
V_CURSOR := DBMS_SQL.OPEN_CURSOR;

DBMS_SQL.PARSE(V_CURSOR, T_STRING, DBMS_SQL.NATIVE);

V_DUMMY := DBMS_SQL.EXECUTE(V_CURSOR);

DBMS_SQL.CLOSE_CURSOR(V_CURSOR);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('NE FONCTIONNE PAS');
END;

FUNCTION VERIF_NON_COMPILE RETURN NUMBER AS

T_VERIF NUMBER(37);
T_NUM NUMBER(37) :=0;

BEGIN
SELECT COUNT(DISTINCT ALO.OBJECT_NAME)
INTO T_VERIF
FROM ALL_OBJECTS ALO
WHERE ALO.OWNER = USER
AND ALO.OWNER NOT IN ('SYS','SYSTEM')
AND ALO.OBJECT_NAME NOT IN ('PAK_ALL_COMPILE')
AND ALO.OBJECT_TYPE IN ('FUNCTION','PROCEDURE','VIEW','PACKAGE','PACKAGE BODY','TRIGGER')
AND ALO.STATUS = 'INVALID';

IF T_VERIF <> 0 THEN
T_NUM := 1;
RETURN T_NUM;
ELSE
T_NUM := 0;
RETURN T_NUM;
END IF;
EXCEPTION
WHEN OTHERS THEN
T_NUM := 0;
RETURN T_NUM;
END;

PROCEDURE PROC_ALL_COMPILE AS

T_STRING_ALTER VARCHAR2(500);

CURSOR CUR_STRING_COMPILE IS
SELECT DISTINCT
'ALTER '||ALO.OBJECT_TYPE||' '||ALO.OBJECT_NAME||' COMPILE'
FROM ALL_OBJECTS ALO
WHERE ALO.OWNER = USER
AND ALO.OWNER NOT IN ('SYS','SYSTEM')
AND ALO.OBJECT_NAME NOT IN ('PAK_ALL_COMPILE')
AND ALO.OBJECT_TYPE IN ('FUNCTION','PROCEDURE','VIEW')
AND ALO.STATUS = 'INVALID'
UNION ALL
SELECT DISTINCT
'ALTER '||DECODE(ALO.OBJECT_TYPE,'PACKAGE BODY','PACKAGE','PACKAGE')||' '||ALO.OBJECT_NAME||' COMPILE'
FROM ALL_OBJECTS ALO
WHERE ALO.OWNER = USER
AND ALO.OWNER NOT IN ('SYS','SYSTEM')
AND ALO.OBJECT_NAME NOT IN ('PAK_ALL_COMPILE')
AND ALO.OBJECT_TYPE IN ('PACKAGE','PACKAGE BODY')
AND ALO.STATUS = 'INVALID'
UNION ALL
SELECT DISTINCT
'ALTER '||DECODE(ALO.OBJECT_TYPE,'PACKAGE BODY','PACKAGE','PACKAGE')||' '||ALO.OBJECT_NAME||' COMPILE BODY'
FROM ALL_OBJECTS ALO
WHERE ALO.OWNER = USER
AND ALO.OWNER NOT IN ('SYS','SYSTEM')
AND ALO.OBJECT_NAME NOT IN ('PAK_ALL_COMPILE')
AND ALO.OBJECT_TYPE IN ('PACKAGE','PACKAGE BODY')
AND ALO.STATUS = 'INVALID'
ORDER BY 1;
BEGIN
OPEN CUR_STRING_COMPILE;
LOOP
FETCH CUR_STRING_COMPILE INTO T_STRING_ALTER;
EXIT WHEN CUR_STRING_COMPILE%NOTFOUND;

EXECUTE_STRING(T_STRING_ALTER);

END LOOP;
CLOSE CUR_STRING_COMPILE;
END;

PROCEDURE PROC_ALL_COMPILE_TRIGGER AS

T_STRING_ALTER VARCHAR2(500);

CURSOR CUR_STRING_COMPILE IS
SELECT DISTINCT
'ALTER '||ALO.OBJECT_TYPE||' '||ALO.OBJECT_NAME||' COMPILE'
FROM ALL_OBJECTS ALO
WHERE ALO.OWNER = USER
AND ALO.OWNER NOT IN ('SYS','SYSTEM')
AND ALO.OBJECT_NAME NOT IN ('PAK_ALL_COMPILE')
AND ALO.OBJECT_TYPE IN ('TRIGGER')
AND ALO.STATUS = 'INVALID'
ORDER BY 1;
BEGIN
OPEN CUR_STRING_COMPILE;
LOOP
FETCH CUR_STRING_COMPILE INTO T_STRING_ALTER;
EXIT WHEN CUR_STRING_COMPILE%NOTFOUND;

EXECUTE_STRING(T_STRING_ALTER);

END LOOP;
CLOSE CUR_STRING_COMPILE;

END PROC_ALL_COMPILE_TRIGGER;

PROCEDURE PROC_CALL_ALL AS

T_NUM NUMBER(37) := 0;
T_COMPTEUR NUMBER(37) := 0;

BEGIN

PROC_ALL_COMPILE;

T_NUM := VERIF_NON_COMPILE;

/* T_NUM = NUMBER OF INVALID OR 5 EQUAL NUMBER OF MAX DEPENDENCIES. You can change */
WHILE T_NUM <> 0 AND T_COMPTEUR < 5
LOOP
T_COMPTEUR := T_COMPTEUR + 1;

PROC_ALL_COMPILE;

T_NUM := VERIF_NON_COMPILE;
END LOOP;

T_COMPTEUR :=0;

PROC_ALL_COMPILE_TRIGGER;

T_NUM := VERIF_NON_COMPILE;

WHILE T_NUM <> 0 AND T_COMPTEUR < 3
LOOP

T_COMPTEUR := T_COMPTEUR + 1;

PROC_ALL_COMPILE_TRIGGER;

T_NUM := VERIF_NON_COMPILE;

END LOOP;

 

END;

END;
/

EXEC PACK_ALL_COMPILE.PROC_CALL_ALL;

 

DROP PACKAGE PACK_ALL_COMPILE;

SELECT COUNT(DISTINCT ALO.OBJECT_NAME)
FROM ALL_OBJECTS ALO
WHERE ALO.OWNER = USER
AND ALO.OWNER NOT IN ('SYS','SYSTEM')
AND ALO.OBJECT_NAME NOT IN ('PAK_ALL_COMPILE')
AND ALO.OBJECT_TYPE IN ('FUNCTION','PROCEDURE','VIEW','PACKAGE','PACKAGE BODY','TRIGGER')
AND ALO.STATUS = 'INVALID';