Oracle PL/SQLブロックでバッチからオブジェクトをコンパイルする

2018年7月13日Oracle,開発

おはようございます。

またまた Oracle の話しになります。

今回は昔作ったちょっとしたツールを公開。

Oracleの機能で、Viewやらプロシージャ、PL/SQLというのがあって、
ひとつずつコンパイルするのは特に問題ないのですが、
一括でコンパイルしたい場合にはオブジェクトブラウザーなどのツールがないとちょっと面倒。

ということでバッチでコンパイルできるようにしました。

オブジェクトをコンパイルする

プロシージャをコンパイルせずに利用できる
PL/SQLブロックというプログラムを実行するバッチとなります。

実行するSQL

次のプログラムをファイルに保存してください。

COMPILE.SQL

SPOOL &1
SET SERVEROUTPUT ON SIZE 1000000
SET LINESIZE 10000
SET ECHO ON
SET PAGESIZE 500

WHENEVER SQLERROR EXIT 10 ROLLBACK
WHENEVER OSERROR EXIT 20 ROLLBACK

BEGIN
    FOR CUR_REC IN (
        SELECT 
            OWNER
            , OBJECT_NAME
            , OBJECT_TYPE
        FROM   
            DBA_OBJECTS
        WHERE  
            OBJECT_TYPE IN ('VIEW', 'PROCEDURE', 'FUNCTION', 'PACKAGE', 'PACKAGE BODY') 
            AND OWNER = '&2'
            AND STATUS = 'INVALID' -- 無効なものだけコンパイルする場合に指定する
        ORDER BY 
            -- コンパイルする順番を指定
            --（パッケージ→パッケージボディの順番じゃないとエラーとなる場合がある）
            DECODE(OBJECT_TYPE, 'VIEW', 1, 'PROCEDURE', 2, 'FUNCTION', 3, 'PACKAGE', 4, 'PACKAGE BODY', 5)
    )
    LOOP
        BEGIN
            -- パッケージボディのみ構文を分ける
            IF CUR_REC.OBJECT_TYPE IN ('VIEW', 'PROCEDURE', 'FUNCTION', 'PACKAGE') THEN
                EXECUTE IMMEDIATE 'ALTER ' || CUR_REC.OBJECT_TYPE || ' "' || CUR_REC.OWNER || '"."' || CUR_REC.OBJECT_NAME || '" COMPILE';
            ELSE
                EXECUTE IMMEDIATE 'ALTER PACKAGE "' || CUR_REC.OWNER || '"."' || CUR_REC.OBJECT_NAME || '" COMPILE BODY';
            END IF;
            -- 成功ログ
            DBMS_OUTPUT.PUT_LINE(' ' || CUR_REC.OBJECT_TYPE || ' : ' || CUR_REC.OWNER || ' : ' || CUR_REC.OBJECT_NAME || ' : OK');
        EXCEPTION
            WHEN OTHERS THEN
                -- 失敗ログ
                DBMS_OUTPUT.PUT_LINE(' ' || CUR_REC.OBJECT_TYPE || ' : ' || CUR_REC.OWNER || ' : ' || CUR_REC.OBJECT_NAME || ' : NG');
        END;
    END LOOP;
END;
/

SPOOL OFF
EXIT 0

SPOOL&1

SETSERVEROUTPUTONSIZE1000000

SETLINESIZE10000

SETECHOON

SETPAGESIZE500

WHENEVERSQLERROREXIT10ROLLBACK

WHENEVEROSERROREXIT20ROLLBACK

BEGIN

FORCUR_RECIN(

SELECT

OWNER

,OBJECT_NAME

,OBJECT_TYPE

FROM

DBA_OBJECTS

WHERE

OBJECT_TYPEIN('VIEW','PROCEDURE','FUNCTION','PACKAGE','PACKAGE BODY')

ANDOWNER='&2'

ANDSTATUS='INVALID'-- 無効なものだけコンパイルする場合に指定する

ORDERBY

-- コンパイルする順番を指定

--（パッケージ→パッケージボディの順番じゃないとエラーとなる場合がある）

DECODE(OBJECT_TYPE,'VIEW',1,'PROCEDURE',2,'FUNCTION',3,'PACKAGE',4,'PACKAGE BODY',5)

)

LOOP

BEGIN

-- パッケージボディのみ構文を分ける

IFCUR_REC.OBJECT_TYPEIN('VIEW','PROCEDURE','FUNCTION','PACKAGE')THEN

EXECUTEIMMEDIATE'ALTER '||CUR_REC.OBJECT_TYPE||' "'||CUR_REC.OWNER||'"."'||CUR_REC.OBJECT_NAME||'" COMPILE';

ELSE

EXECUTEIMMEDIATE'ALTER PACKAGE "'||CUR_REC.OWNER||'"."'||CUR_REC.OBJECT_NAME||'" COMPILE BODY';

ENDIF;

-- 成功ログ

DBMS_OUTPUT.PUT_LINE(' '||CUR_REC.OBJECT_TYPE||' : '||CUR_REC.OWNER||' : '||CUR_REC.OBJECT_NAME||' : OK');

EXCEPTION

WHENOTHERSTHEN

-- 失敗ログ

DBMS_OUTPUT.PUT_LINE(' '||CUR_REC.OBJECT_TYPE||' : '||CUR_REC.OWNER||' : '||CUR_REC.OBJECT_NAME||' : NG');

END;

ENDLOOP;

END;

SPOOLOFF

EXIT0

SQL呼び出しバッチ

COMPILE.SQLを呼び出すバッチです。
環境にあわせて変数の設定をして保存してください。

COMPILE.bat

@echo off

REM 実行するSQLファイル
SET SQLFILE=COMPILE.SQL

REM ログファイル（スプール）
SET LOGFILE=COMPILE.LOG

REM 対象ユーザー
SET TARGET=

REM パスワード
SET PASS=

REM TNSサービス名
SET SERVICE_NAME=

REM 実行
sqlplus %TARGET%/%PASS%@%SERVICE_NAME% @%SQLFILE% "%LOGFILE%" "%TARGET%"

pause