【Oracle】DBMS_UTILITY完全ガイド｜FORMAT_ERROR_BACKTRACE・COMPILE_SCHEMA・GET_TIME など実用関数を徹底解説

-- エラーログテーブルへの記録パターン（実務でよく使う構成）
CREATE OR REPLACE PROCEDURE log_and_raise(p_proc_name VARCHAR2) AS
BEGIN
    -- エラーログテーブルに記録する（AUTONOMOUS_TRANSACTION で分離）
    INSERT INTO error_log(log_date, proc_name, error_stack, error_backtrace)
    VALUES(
        SYSTIMESTAMP,
        p_proc_name,
        DBMS_UTILITY.FORMAT_ERROR_STACK,       -- ORA-xxxxx: メッセージ
        DBMS_UTILITY.FORMAT_ERROR_BACKTRACE    -- エラーが発生した行番号・ファイル
    );
    -- RAISE で再送出すると BACKTRACE がこの行に更新されるため先にログを書く
    RAISE;
END log_and_raise;
/

-- FORMAT_ERROR_BACKTRACE の出力例:
-- ORA-06512: at "HR.PROCESS_ORDER", line 45
-- ORA-06512: at "HR.BATCH_PROCESS", line 12
-- 上記から: HR.PROCESS_ORDER の 45 行目で例外が発生したとわかる

-- FORMAT_ERROR_STACK の出力例:
-- ORA-01400: cannot insert NULL into ("HR"."ORDERS"."ORDER_ID")
-- ORA-06512: at "HR.PROCESS_ORDER", line 45

-- DBMS_UTILITY.FORMAT_CALL_STACK の使い方（例外ハンドラ外でも使える）
DECLARE
    v_stack VARCHAR2(2000);
BEGIN
    -- 現在の呼び出しスタックを取得する
    v_stack := DBMS_UTILITY.FORMAT_CALL_STACK;
    DBMS_OUTPUT.PUT_LINE(v_stack);
    -- 出力例:
    -- ----- PL/SQL Call Stack -----
    --   object      line  object
    --   handle    number  name
    -- 0x3a1234bc       5  anonymous block
END;
/

-- スキーマ内の無効オブジェクトを確認する
SELECT object_type, COUNT(*) AS cnt
FROM DBA_OBJECTS
WHERE owner = 'HR' AND status = 'INVALID'
GROUP BY object_type
ORDER BY object_type;

-- COMPILE_SCHEMA を実行する（SYS または DBA 権限が必要）
BEGIN
    DBMS_UTILITY.COMPILE_SCHEMA(
        schema   => 'HR',      -- 対象スキーマ（大文字）
        compile_all => FALSE    -- FALSE: INVALID なオブジェクトのみ / TRUE: 全オブジェクト
    );
END;
/

-- Oracle 10g 以降は第3引数で再コンパイルの順序を制御できる
BEGIN
    DBMS_UTILITY.COMPILE_SCHEMA(
        schema      => 'HR',
        compile_all => FALSE,
        reuse_settings => TRUE   -- TRUE: オブジェクトのコンパイル設定（PLSQL_CODE_TYPE等）を引き継ぐ
    );
END;
/

-- 再コンパイル後に INVALID が残っていないか確認する
SELECT object_name, object_type, last_ddl_time
FROM DBA_OBJECTS
WHERE owner = 'HR' AND status = 'INVALID'
ORDER BY object_type, object_name;
-- INVALID が残る場合は依存関係エラー（他オブジェクトの列名変更など）が原因

DECLARE
    v_schema    VARCHAR2(30);
    v_part1     VARCHAR2(30);   -- パッケージ名またはオブジェクト名
    v_part2     VARCHAR2(30);   -- パッケージ内のプロシージャ名
    v_dblink    VARCHAR2(30);   -- データベースリンク名
    v_part1_type NUMBER;        -- オブジェクト種別
    v_object_id  NUMBER;        -- オブジェクトID
BEGIN
    -- プロシージャ名を解決する
    DBMS_UTILITY.NAME_RESOLVE(
        name          => 'HR.PROCESS_ORDER',   -- 解決するオブジェクト名
        context       => 1,                      -- 1: PL/SQL コンテキスト
        schema        => v_schema,
        part1         => v_part1,
        part2         => v_part2,
        dblink        => v_dblink,
        part1_type    => v_part1_type,
        object_number => v_object_id
    );

    DBMS_OUTPUT.PUT_LINE('スキーマ: ' || v_schema);
    DBMS_OUTPUT.PUT_LINE('オブジェクト: ' || v_part1);
    DBMS_OUTPUT.PUT_LINE('サブプログラム: ' || NVL(v_part2, '(なし)'));
    DBMS_OUTPUT.PUT_LINE('オブジェクトID: ' || v_object_id);
    -- part1_type の値: 7=プロシージャ, 8=ファンクション, 9=パッケージ, 12=トリガー など

EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('解決失敗: ' || SQLERRM);
END;
/

-- パッケージ内プロシージャを解決する例
-- NAME_RESOLVE('HR.BATCH_PKG.PROCESS_MONTHLY', 1, v_schema, v_part1, v_part2, ...);
-- → v_part1 = 'BATCH_PKG', v_part2 = 'PROCESS_MONTHLY'

DECLARE
    v_start  PLS_INTEGER;
    v_end    PLS_INTEGER;
    v_elapsed_cs PLS_INTEGER;   -- センチ秒 (1/100秒)
BEGIN
    -- 開始時刻を記録する
    v_start := DBMS_UTILITY.GET_TIME;

    -- 計測したい処理を実行する
    FOR i IN 1..100000 LOOP
        NULL;
    END LOOP;

    -- 終了時刻を記録する
    v_end := DBMS_UTILITY.GET_TIME;

    -- 経過センチ秒を計算する
    -- GET_TIME の戻り値は BINARY_INTEGER で符号なし整数のオーバーフローに注意
    v_elapsed_cs := v_end - v_start;

    DBMS_OUTPUT.PUT_LINE(
        '経過時間: ' || v_elapsed_cs || ' センチ秒 (' ||
        ROUND(v_elapsed_cs / 100, 2) || ' 秒)'
    );
END;
/

DECLARE
    v_list   VARCHAR2(500) := 'EMPLOYEES,DEPARTMENTS,ORDERS,CUSTOMERS';
    v_tablen BINARY_INTEGER;
    v_tab    DBMS_UTILITY.UNCL_ARRAY;   -- UNCL_ARRAY: 文字列配列型
    v_result VARCHAR2(500);
BEGIN
    -- カンマ区切り文字列 → PL/SQL テーブル
    DBMS_UTILITY.COMMA_TO_TABLE(
        list   => v_list,
        tablen => v_tablen,   -- 出力: 要素数
        tab    => v_tab       -- 出力: 要素の配列
    );

    DBMS_OUTPUT.PUT_LINE('要素数: ' || v_tablen);
    FOR i IN 1..v_tablen LOOP
        DBMS_OUTPUT.PUT_LINE(i || ': ' || v_tab(i));
    END LOOP;
    -- 出力:
    -- 要素数: 4
    -- 1: EMPLOYEES
    -- 2: DEPARTMENTS
    -- 3: ORDERS
    -- 4: CUSTOMERS

    -- PL/SQL テーブル → カンマ区切り文字列
    DBMS_UTILITY.TABLE_TO_COMMA(
        tab    => v_tab,
        tablen => v_tablen,
        list   => v_result    -- 出力: カンマ区切り文字列
    );
    DBMS_OUTPUT.PUT_LINE('結果: ' || v_result);
    -- 出力: 結果: EMPLOYEES,DEPARTMENTS,ORDERS,CUSTOMERS
END;
/

-- Oracle 12c 以降で使用可能
DECLARE
    v_input  CLOB := 'SELECT * FROM emp_details_view WHERE department_id = 50';
    v_output CLOB;
BEGIN
    DBMS_UTILITY.EXPAND_SQL_TEXT(
        input_sql_text  => v_input,
        output_sql_text => v_output
    );
    -- ビューの定義が展開されたフル SQL が返される
    DBMS_OUTPUT.PUT_LINE(DBMS_LOB.SUBSTR(v_output, 4000, 1));
END;
/

-- WITH 句（CTE）も展開される
DECLARE
    v_input  CLOB := 'WITH dept_avg AS (SELECT department_id, AVG(salary) avg_sal FROM employees GROUP BY department_id) SELECT * FROM dept_avg WHERE avg_sal > 10000';
    v_output CLOB;
BEGIN
    DBMS_UTILITY.EXPAND_SQL_TEXT(v_input, v_output);
    DBMS_OUTPUT.PUT_LINE(DBMS_LOB.SUBSTR(v_output, 4000, 1));
END;
/

-- RAC 環境かどうかを確認する
DECLARE
    v_is_rac BOOLEAN;
BEGIN
    v_is_rac := DBMS_UTILITY.IS_CLUSTER_DATABASE;
    IF v_is_rac THEN
        DBMS_OUTPUT.PUT_LINE('RAC 環境です');
    ELSE
        DBMS_OUTPUT.PUT_LINE('シングルインスタンスです');
    END IF;
END;
/

-- オブジェクトの依存関係を確認する
-- GET_DEPENDENCY は DBA_DEPENDENCIES ビューに似た情報を返す
BEGIN
    DBMS_UTILITY.GET_DEPENDENCY(
        type   => 'PACKAGE',     -- オブジェクト種別
        schema => 'HR',          -- スキーマ
        name   => 'BATCH_PKG'   -- オブジェクト名
    );
    -- 結果は DBMS_OUTPUT に出力される
    -- 依存しているオブジェクト一覧が表示される
END;
/

-- DB_VERSION でバージョン情報を取得する
DECLARE
    v_version   VARCHAR2(100);
    v_compatible VARCHAR2(100);
BEGIN
    DBMS_UTILITY.DB_VERSION(v_version, v_compatible);
    DBMS_OUTPUT.PUT_LINE('バージョン: ' || v_version);
    DBMS_OUTPUT.PUT_LINE('互換性: ' || v_compatible);
    -- 出力例:
    -- バージョン: 19.0.0.0.0
    -- 互換性: 19.0.0
END;
/