Oracle® 非公式 SQL, PL/SQL & DBA's リファレンス

DBMS_OUTPUT パッケージの使用

PL/SQL において DBMS_OUTPUT パッケージはデバッグ目的などで標準出力にメッセージを表示するためのおそらく唯一のパッケージで開発作業中には利用率ナンバーワンのパッケージである。その多くは開発フェーズが終了すると呼び出しのオーバヘッドをなくす為にコメント化されるか無効化される。

使用方法は以下のように簡単である。

BEGIN
	FOR i in 1..7 LOOP
		FOR j IN 1..9 LOOP
			DBMS_OUTPUT.PUT('-');
		END LOOP;
		DBMS_OUTPUT.PUT('+');
	END LOOP;
	DBMS_OUTPUT.NEW_LINE;
	DBMS_OUTPUT.PUT_LINE('12345678901234567890');
END;
/
 
実行結果
---------+---------+---------+---------+---------+---------+---------+
12345678901234567890

DBMS_OUTPUT.PUT_LINE(文字列) は文字列を出力後に改行を出力する。
DBMS_OUTPUT.PUT(文字列) は文字列だけを出力する。ユーザーが 最後に PUT_LINE や NEW_LINE を使うなどして改行を出力しなければ表示されない。

DBMS_OUTPUT 使用時の注意

DBMS_OUTPUT パッケージの使用目的としては、デバック時の出力と簡易な終了ステータス情報の出力先として使用している事が多いが制限事項が多い。
これは DBMS_OUTPUT が出力先として標準出力に直接つながっていないことによる。

DBMS_OUTPUT のメッセージが出力される過程（予想）は以下のとおり

1. PUT_LINE などの出力命令のプロシージャの実行
2. メッセージのすべてをオラクルのメモリバッファに一時格納しておく
3. コール処理が完了時にバッファの内容を標準出力へ書き込み(※1)

ここで問題となるのが (2) のバッファ領域である。 PUT_LINE などで使用できる一行のバッファサイズが 255 バイトであり、全体のバッファサイズはデフォルトで 約 2K / 20K バイト(※2)、 最大で 約 1M バイト(※2) である。
このため、用途次第では、すぐにバッファサイズを超過して ORA-20000: ORU-10027: buffer overflow, limit of 20000 bytes とエラーとなる。(※3)

この一時バッファはコール処理が完了するまで標準出力にフラッシュする手段が用意されていない。 つまり、処理の進行情報の経過を出力しても終了時にフラッシュされるのでリアルタイムで経過情報が欲しい場合には意味がない。

メッセージをメモリ上で管理しているものの残念ながらリモートプロシージャ（自己参照のリンク含む）での DBMS_OUTPUT 出力した結果は実行端末に表示されないようである。

(※1) SQLPlus において SERVEROUTPUT システム変数が OFF の場合や DBMS_OUTPUT.DISABLE を呼び出している場合にはメッセージは出力されない。また DBMS_SESSION.RESET_PACKAGE によるパッケージのインスタンス破棄処理よりも後に実行されているようである。 参考： ORA-04068:ORA-04068: パッケージの既存状態は廃棄されました

(※2) PUT_LINE 等の１行のバッファサイズの変更する方法は２つ。 SQL*Plus からは SERVEROUTPUT システム変数、 PL/SQL からは DBMS_OUTPUT.ENABLE(バイト数) によって最大 1000000 バイト（約 1M バイト) まで拡張する事が可能

何故か DBMS_OUTPUT.ENABLE (20000バイト) と SET SERVEROUTPUT (2000バイト) でデフォルトのバッファサイズが異なる。

(※3) DBMS_OUTPUT を使った大量の出力結果をスプールしてファイルに書き出そうという試みも成功しない。

PUT_LINE の最大長と全体バッファサイズの拡張

Oracle 10g R2(※) から一行の最大長が 32KB に、そして全体のバッファサイズは無制限に拡張されている。

DBMS_OUTPUT.ENABLE(NULL)
または SQL*Plus から
SET SERVEROUTPUT ON 

にて無制限状態に設定される。SET ARRAYSIZE（システム変数）によってバッファメモリのチューニングができると書かれている。(注意:メモリを大量に消費するかもしれない)

(※) おそらくオフィシャルではないという形で Oracle 10g R2 より前のリリースでも全体サイズ 約１ＭＢのリミット解除の方だけは実装されているようである。

Oracle 10g R1 以前の場合には、以下のように使用できる最大のバッファサイズを指定することで 2000 〜1000000バイト(約1MB) までのバッファの拡張が可能

DBMS_OUTPUT.ENABLE(1000000)
または SQL*Plus から
SET SERVEROUTPUT ON SIZE 1000000

---