Oracle Objectsの強制コンパイル PL/SQL無名ブロック

compile_objects.sql

--
-- Oracle Objectsの強制コンパイル
-- 多くの場所で使われているあるOracle Objectを変更すると
-- Oracle ObjectのSTATUSが'INVALID'になってしまうことがある。
-- 当PL/SQLブロックはSTATUSが'INVALID'のOBJECTに対して
-- ALTER OBJECT_TYPE OBJECT_NAME COMPILE ;
-- を実行してOracle Objectを強制的にコンパイルし、
-- システムの停止時間を最小限に抑える。
--
-- 昔作ったコードをちょっと改造
-- http://otn.oracle.co.jp/otn_pl/otn_tool/code_detail?n_code_id=1103
--
SET LINESIZE 250
SET PAGESIZE 50000
SET SERVEROUTPUT ON SIZE UNLIMITED
SET TAB OFF
SET TRIMSPOOL ON
-- SPOOL pass includes CONNECT_IDENTIFIER, USER_NAME and SYSDATE
COLUMN USER_NAME    NEW_VALUE USER_NAME
COLUMN SYSTEM_DATE  NEW_VALUE SYSTEM_DATE
SELECT USER || '_' AS USER_NAME
     , TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS') AS SYSTEM_DATE
FROM DUAL ;
SPOOL compile_objects_&&_CONNECT_IDENTIFIER._&&USER_NAME&&SYSTEM_DATE..log
-- Windows ↑,  UNIX/Linux では ↓ を有効に
-- SPOOL compile_objects_\&&_CONNECT_IDENTIFIER._\&&USER_NAME\&&SYSTEM_DATE..log
DECLARE
    MY_NAME CONSTANT  VARCHAR2(19) := 'compile_objects.sql' ;
    L_SQLERRM         VARCHAR2(2048) ;
    RETRY_MAX CONSTANT  NUMBER := 6 ;  -- 繰り返し回数。依存関係が複雑な環境では値を大きくする必要があるかもしれない。
BEGIN
-- OBJECTから呼ばれているOBJECTがINVALIDで、さらにそのOBJECTから呼ばれて
-- いるOBJECTがINVALID だったりする依存関係があると、一度のコンパイル
-- ではVALIDにならないことがあるので何度かコンパイルする。
--
    FOR RETRY_COUNT IN 1..RETRY_MAX LOOP
        DBMS_OUTPUT.PUT_LINE('-- ' || TO_CHAR(RETRY_COUNT) || ' 回目') ;
        DECLARE
            CURSOR CUR_OBJ
            IS
                SELECT
                    'ALTER '
                  || CASE
                         WHEN OB.OBJECT_TYPE = 'PACKAGE BODY' THEN
                             'PACKAGE'
                         WHEN OB.OBJECT_TYPE = 'TYPE BODY' THEN
                             'TYPE'
                         ELSE
                             OB.OBJECT_TYPE
                     END
                  || ' ' || OB.OBJECT_NAME || ' '
                  || CASE
                         WHEN OB.OBJECT_TYPE IN('PACKAGE BODY', 'TYPE BODY') THEN
                             'COMPILE BODY'
                         ELSE
                             'COMPILE'
                     END AS COMPILE_OBJECT
                   , OB.OBJECT_TYPE
                   , OB.OBJECT_NAME
                FROM USER_OBJECTS OB
                WHERE
                    OB.STATUS = 'INVALID'
                AND OB.OBJECT_NAME NOT LIKE 'BIN$%' -- except trash box, since Oracle10.1.0
--              AND OB.OBJECT_TYPE <> 'MATERIALIZED VIEW'  -- REFRESH情報が溜まるとSTATUSがINVALIDになるため、COMPILEすべきか現場毎の事情に合わせる
                ORDER BY
                    OB.OBJECT_NAME
                  , OB.OBJECT_TYPE ;
        BEGIN
            FOR REC_OBJ IN CUR_OBJ LOOP
                DBMS_OUTPUT.PUT_LINE(REC_OBJ.COMPILE_OBJECT || ' ;') ;
                BEGIN
                    EXECUTE IMMEDIATE REC_OBJ.COMPILE_OBJECT ;
                EXCEPTION
                    WHEN OTHERS THEN
                        IF RETRY_COUNT = RETRY_MAX THEN
                            DBMS_OUTPUT.PUT_LINE('SHOW ERROR ' || REC_OBJ.OBJECT_TYPE || ' ' || REC_OBJ.OBJECT_NAME) ;
                            -- 表示された内容をsqlpusにcopy and pasteする。
                        END IF ;
                END ;
            END LOOP ;
        END ;
    END LOOP ;
EXCEPTION
    WHEN OTHERS THEN
        L_SQLERRM := SQLERRM ;
        DBMS_OUTPUT.PUT_LINE(MY_NAME || ' ERROR:' || L_SQLERRM) ;
        RAISE ;
END ;
/
SPOOL OFF
SET LINESIZE 80