【Oracle】DBMS_OUTPUTの使い方完全ガイド｜PUT_LINE・SET SERVEROUTPUT・バッファサイズ・ログテーブル・PL/SQLデバッグテクニック

【Oracle】DBMS_OUTPUTの使い方完全ガイド｜PUT_LINE・SET SERVEROUTPUT・バッファサイズ・ログテーブル・PL/SQLデバッグテクニック Oracle

DBMS_OUTPUT は PL/SQL の実行中に文字列をバッファに書き込み、実行終了後にクライアント（SQL*Plus・SQL Developer・DBeaver 等）に出力するパッケージです。デバッグ用の出力・進捗確認・変数値のトレースに広く使われます。

この記事でわかること

  • DBMS_OUTPUT.PUT_LINE の基本と SET SERVEROUTPUT ON の設定
  • PUT・NEW_LINE・PUT_LINE の違い
  • バッファサイズの変更（DBMS_OUTPUT.ENABLE）
  • ループ進捗・変数値・例外情報を出力するデバッグパターン
  • 本番環境で使えるログテーブルへの書き込みパターン
  • DBMS_OUTPUT の制限と代替手段（UTL_FILE・DBMS_APPLICATION_INFO）
スポンサーリンク

DBMS_OUTPUT の基本：PUT_LINE で出力する

SET SERVEROUTPUT ON と基本的な PUT_LINE
-- SQL*Plus / SQL Developer で出力を有効化する（実行前に1回だけ必要）
SET SERVEROUTPUT ON

-- 基本的な出力
BEGIN
    DBMS_OUTPUT.PUT_LINE('Hello, Oracle!');
    DBMS_OUTPUT.PUT_LINE('今日の日付: ' || TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS'));
    DBMS_OUTPUT.PUT_LINE('1 + 1 = ' || TO_CHAR(1 + 1));
END;
/
-- 出力:
-- Hello, Oracle!
-- 今日の日付: 2024/03/15 10:30:00
-- 1 + 1 = 2

-- PUT と NEW_LINE の使い方（PUT_LINE = PUT + NEW_LINE）
BEGIN
    DBMS_OUTPUT.PUT('A');         -- 改行なしで "A" をバッファに追加
    DBMS_OUTPUT.PUT('B');         -- 改行なしで "B" を追加
    DBMS_OUTPUT.NEW_LINE;           -- 改行を追加 → "AB" が1行として出力される
    DBMS_OUTPUT.PUT_LINE('CD');   -- "CD" を1行出力（PUT + NEW_LINE と同等）
END;
/
-- 出力:
-- AB
-- CD

バッファサイズの設定（DBMS_OUTPUT.ENABLE）

DBMS_OUTPUT はバッファにすべての出力を蓄積し、PL/SQL ブロック終了後にクライアントへ送ります。デフォルトのバッファサイズは 20000 バイトで、大量の出力が必要な場合は DBMS_OUTPUT.ENABLE でサイズを変更します。

DBMS_OUTPUT.ENABLE でバッファサイズを設定する
-- バッファサイズを 1MB に設定（UNLIMITED も指定可能）
BEGIN
    DBMS_OUTPUT.ENABLE(buffer_size => 1000000);   -- 1MB
END;
/

-- または SQL*Plus の SET SERVEROUTPUT コマンドでサイズ指定
SET SERVEROUTPUT ON SIZE 1000000    -- 1MB

-- バッファが溢れると ORU-10027: buffer overflow エラーになる
-- → DBMS_OUTPUT.ENABLE(NULL) で UNLIMITED に設定すると溢れない

BEGIN
    DBMS_OUTPUT.ENABLE(NULL);   -- バッファ無制限

    FOR i IN 1 .. 100000 LOOP
        DBMS_OUTPUT.PUT_LINE('行 ' || i);
    END LOOP;
END;
/
-- 大量の出力でも ORU-10027 が発生しない

デバッグに役立つ出力パターン

変数値・ループ進捗のトレース
DECLARE
    v_count    NUMBER := 0;
    v_dept_id  employees.department_id%TYPE;
    v_salary   employees.salary%TYPE;
BEGIN
    -- 変数の値を出力
    SELECT MAX(salary) INTO v_salary FROM employees;
    DBMS_OUTPUT.PUT_LINE('最大給与: ' || v_salary);

    -- ループの進捗を確認（大量データ処理時の途中経過確認）
    FOR rec IN (SELECT department_id, COUNT(*) AS cnt
                FROM employees GROUP BY department_id
                ORDER BY department_id)
    LOOP
        v_count := v_count + rec.cnt;
        DBMS_OUTPUT.PUT_LINE(
            '部門 ' || LPAD(rec.department_id, 5) ||
            ' : ' || LPAD(rec.cnt, 4) || '名' ||
            '（累計: ' || v_count || '）'
        );
    END LOOP;

    DBMS_OUTPUT.PUT_LINE('--- 処理完了: 合計 ' || v_count || '件 ---');
END;
/
例外発生時の詳細情報を出力する
BEGIN
    -- 意図的に例外を発生させる
    DECLARE
        v_val NUMBER;
    BEGIN
        SELECT employee_id INTO v_val FROM employees WHERE 1=0;  -- データなし

    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            DBMS_OUTPUT.PUT_LINE('[ERROR] NO_DATA_FOUND 発生');
            DBMS_OUTPUT.PUT_LINE('  SQLCODE: ' || SQLCODE);
            DBMS_OUTPUT.PUT_LINE('  SQLERRM: ' || SQLERRM);
            -- エラーのスタックトレースも確認できる（Oracle 12c 以降）
            DBMS_OUTPUT.PUT_LINE('  BACKTRACE: ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('[ERROR] 予期しない例外: ' || SQLERRM);
            DBMS_OUTPUT.PUT_LINE('  BACKTRACE: ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
            RAISE;  -- 例外を再発生させる
    END;
END;
/

本番環境で使えるログテーブルへの書き込みパターン

本番環境では DBMS_OUTPUT で出力してもアプリケーション側に表示されません。長時間バッチや自動実行のジョブでは、ログをテーブルに書き込む方法が実用的です。

ログテーブルの作成
-- 汎用ログテーブル
CREATE TABLE app_log (
    log_id    NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    log_time  TIMESTAMP(3) DEFAULT SYSTIMESTAMP NOT NULL,
    log_level VARCHAR2(10) DEFAULT 'INFO'
              CHECK (log_level IN ('DEBUG', 'INFO', 'WARN', 'ERROR')),
    proc_name VARCHAR2(100),
    message   VARCHAR2(4000),
    err_code  NUMBER,
    err_msg   VARCHAR2(4000)
);
ログパッケージの実装例
-- ログ書き込みプロシージャ
CREATE OR REPLACE PROCEDURE write_log(
    p_level     VARCHAR2,
    p_proc_name VARCHAR2,
    p_message   VARCHAR2,
    p_err_code  NUMBER   DEFAULT NULL,
    p_err_msg   VARCHAR2 DEFAULT NULL
)
AS
    PRAGMA AUTONOMOUS_TRANSACTION;   -- 親トランザクションから独立してコミット
BEGIN
    INSERT INTO app_log (log_level, proc_name, message, err_code, err_msg)
    VALUES (p_level, p_proc_name, SUBSTR(p_message, 1, 4000),
            p_err_code, SUBSTR(p_err_msg, 1, 4000));
    COMMIT;   -- 親トランザクションが ROLLBACK しても、ログは残る
END write_log;
/

-- 使い方: バッチ処理のログ記録
CREATE OR REPLACE PROCEDURE batch_update_salary AS
    v_updated NUMBER := 0;
BEGIN
    write_log('INFO', 'batch_update_salary', '処理開始');

    UPDATE employees SET salary = salary * 1.05
    WHERE department_id = 50;
    v_updated := SQL%ROWCOUNT;

    write_log('INFO', 'batch_update_salary',
              v_updated || '件の給与を更新しました');
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        write_log('ERROR', 'batch_update_salary', '給与更新エラー',
                  SQLCODE, SQLERRM || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
        ROLLBACK;
        RAISE;
END;
/
PRAGMA AUTONOMOUS_TRANSACTION でログを独立してコミット
ログ書き込みプロシージャに PRAGMA AUTONOMOUS_TRANSACTION を付けると、呼び出し元のトランザクションが ROLLBACK されてもログレコードはコミットされたまま残ります。バッチ処理でエラーが発生して ROLLBACK した場合でも、どこまで処理が進んでいたか確認できます。

DBMS_OUTPUT の制限と代替手段

制限・問題点 対策・代替手段
PL/SQL 終了後にまとめて出力（リアルタイムではない） DBMS_APPLICATION_INFO でリアルタイム進捗を V$SESSION に記録
バッファが溢れると ORU-10027 エラー DBMS_OUTPUT.ENABLE(NULL) または UTL_FILE でファイルに書き出し
本番の自動実行（DBMS_SCHEDULER 等）では出力されない ログテーブル（AUTONOMOUS_TRANSACTION）または UTL_FILE を使う
VARCHAR2 の最大長（32767バイト）を超える出力は1行に収まらない 長いメッセージは複数回に分けて PUT_LINE するか CLOB でログテーブルに書く
DBMS_APPLICATION_INFO でリアルタイム進捗を記録する
-- DBMS_APPLICATION_INFO: V$SESSION の MODULE/ACTION/CLIENT_INFO に書き込む
-- → 別セッションから V$SESSION をポーリングしてリアルタイムで進捗確認できる

BEGIN
    -- ジョブ開始時にモジュール名を設定
    DBMS_APPLICATION_INFO.SET_MODULE('batch_update_salary', '初期化');

    FOR i IN 1 .. 1000 LOOP
        -- ループ内で進捗を更新
        DBMS_APPLICATION_INFO.SET_ACTION('処理中: ' || i || '/1000');
        -- ... 実際の処理 ...
    END LOOP;

    DBMS_APPLICATION_INFO.SET_MODULE(NULL, NULL);   -- クリア
END;
/

-- 別セッションから確認
SELECT module, action, client_info
FROM v$session
WHERE module = 'batch_update_salary';
-- ACTION: 処理中: 350/1000（リアルタイムで更新されている）

まとめ

  • SET SERVEROUTPUT ON：SQL*Plus・SQL Developer でまず実行。サイズ指定で大量出力に対応
  • DBMS_OUTPUT.PUT_LINE：変数の値・ループ進捗・例外情報のデバッグに便利
  • DBMS_UTILITY.FORMAT_ERROR_BACKTRACE：例外発生箇所のスタックトレースを取得
  • PRAGMA AUTONOMOUS_TRANSACTION：ログを親トランザクションから独立してコミット
  • DBMS_APPLICATION_INFO：リアルタイムで V$SESSION に進捗を記録。長時間バッチの監視に有効

PL/SQL の例外処理の詳細はPL/SQL 例外処理（Exception Handling）完全ガイドを、ファイルへの出力が必要な場合はUTL_FILE完全ガイドも参照してください。