DBMS_OUTPUT.PUT_LINE()/PUT()の出力制限 Tweet
Oracle10g R2からDBMS_OUTPUT.PUT_LINE()/PUT()関数で出力できる１行最大サイズが、２５５バイトから３２７６７バイトへ拡張されたことはご存知だろうか？
PL/SQLでプログラミングしている方ならほとんどの方がお世話になったことがあると思うDBMS_OUTPUT.PUT_LINE()/PUT()関数。これらの関数は、Oracle7のころから、ず〜〜〜〜〜っと２５５バイトが最大サイズだった。
それが、Oracle10g R2になって、いきなり３２７６７バイト（このサイズは、VARCHAR2型のPL/SQL内での最大サイズに等しい）まで拡張された。（うれしいというか、便利にというか、楽にはなったかもしれない。）
だが、いままで工夫して利用してきているだけに、意外とそのまんまになっていたり、３２７６７バイトまで拡張されたということを知らない方も多いようなので、ネタとしてはあまり新しいとは思えないのだが取り上げておく事にした。
ということで、Oracle10g R1 10.1.0.4.0とOracle10g R2 10.2.0.1.0を利用して実際に違いを確認。
以下の無名PL/SQLブロックでは、 ２５５バイト／２５６バイト／３２７６７バイト／３２７６８バイトの各文字列をDBMS_OUTPUT.PUT_LINE()関数で出力するコードである。
DBMS_OUTPUT.PUT_LINE()/PUT()関数の制限により、Oracle10g R2より前のリリースでは、２５６バイト以上の文字列を出力しようと場合／Oracle10g R2以降では３２７６８バイト以上の文字列を出力しようとするとエラーになる。
● Oracle10g R2 10.2.0.1.0の例
３２７６８バイトの文字列を出力しようとした場合だけがエラーになっていることが確認できる。
SCOTT> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SCOTT> set serveroutput on size 100000
SCOTT> l
1 declare
2 str255 varchar2(255) := rpad('255',255,'*');
3 str256 varchar2(256) := rpad('256',256,'*');
4 str32767 varchar2(32767) := rpad('32767',32767,'*');
5 begin
6 dbms_output.put_line(str255);
7 begin
8 dbms_output.put_line(str256);
9 exception
10 when others then
11 dbms_output.put_line(sqlerrm());
12 end;
13 dbms_output.put_line(str32767);
14 begin
15 dbms_output.put_line(str32767||'*');
16 exception
17 when others then
18 dbms_output.put_line(sqlerrm());
19 end;
20* end;
SCOTT> /
255*****************************************************************************
********************************************************************************
********************************************************************************
***************
256*****************************************************************************
********************************************************************************
********************************************************************************
****************
32767***************************************************************************
********************************************************************************
・・・・・中略・・・・・
********************************************************************************
********************************************************************************
********************************************************************************
***********************************************
ORA-20000: ORU-10028: line length overflow, limit of 32767 bytes per line
PL/SQLプロシージャが正常に完了しました。
SCOTT>
● Oracle10g R1 10.1.0.4.0の例
２５６バイト以上の文字列を出力しようとした場合、全てがエラー。当然！
SCOTT> conn scott@fishtank
パスワードを入力してください:
接続されました。
SCOTT> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - Prod
PL/SQL Release 10.1.0.4.0 - Production
CORE 10.1.0.4.0 Production
TNS for Linux: Version 10.1.0.4.0 - Production
NLSRTL Version 10.1.0.4.0 - Production
SCOTT> set serveroutput on size 100000
SCOTT> l
1 declare
2 str255 varchar2(255) := rpad('255',255,'*');
3 str256 varchar2(256) := rpad('256',256,'*');
4 str32767 varchar2(32767) := rpad('32767',32767,'*');
5 begin
6 dbms_output.put_line(str255);
7 begin
8 dbms_output.put_line(str256);
9 exception
10 when others then
11 dbms_output.put_line(sqlerrm());
12 end;
13 begin
14 dbms_output.put_line(str32767);
15 exception
16 when others then
17 dbms_output.put_line(sqlerrm());
18 end;
19 begin
20 dbms_output.put_line(str32767||'*');
21 exception
22 when others then
23 dbms_output.put_line(sqlerrm());
24 end;
25* end;
SCOTT> /
255*****************************************************************************
********************************************************************************
********************************************************************************
***************
ORA-20000: ORU-10028: line length overflow, limit of 255 chars per line
ORA-20000: ORU-10028: line length overflow, limit of 255 chars per line
ORA-20000: ORU-10028: line length overflow, limit of 255 chars per line
PL/SQLプロシージャが正常に完了しました。
SCOTT>
以上で、Oracle10g R2以降でのDBMS_OUTPUT.PUT_LINE()/PUT()関数の拡張は確認できた。PL/SQL内では、３２７６７バイトがVARCHAR2型の最大サイズなので、３２７６７バイト以内の出力であればDBMS_OUTPUT.PUT_LINE()/PUT()関数１行で済むということはかなり便利だ。（３２７６８バイト以上の出力には今まで通りのコードが必要だが。。。）
以下のサンプルコードは、最大３２７６７バイトまでの文字列を引数で受け取り、DBMS_OUTPUT.PUT_LINE()で表示するという単純なコードだ。
● Oracle10g R1 10.1.0.4.0
PL/SQLの条件付きコンパイルは、Oracle10g R1ではデフォルトでは機能しない。隠しパラメータをTRUEに設定する必要がある。
SYS> show parameter _plsql_conditional
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
_plsql_conditional_compilation boolean TRUE
SYS> conn scott@fishtank
パスワードを入力してください:
接続されました。
SCOTT> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - Prod
PL/SQL Release 10.1.0.4.0 - Production
CORE 10.1.0.4.0 Production
TNS for Linux: Version 10.1.0.4.0 - Production
NLSRTL Version 10.1.0.4.0 - Production
SCOTT> set serveroutput on size 100000
SCOTT> l
1 CREATE OR REPLACE PROCEDURE dbms_output_test
2 (
3 i_str IN VARCHAR2
4 )
5 IS
6 v_str VARCHAR2(32767);
7 $IF DBMS_DB_VERSION.VERSION <= 9
8 OR (DBMS_DB_VERSION.VERSION = 10
9 AND DBMS_DB_VERSION.RELEASE < 2)
10 $THEN
11 v_pices PLS_INTEGER;
12 C_NumOfChars CONSTANT PLS_INTEGER := 85;
13 $END
14 BEGIN
15 v_str := i_str;
16 $IF DBMS_DB_VERSION.VERSION <= 9
17 OR (DBMS_DB_VERSION.VERSION = 10
18 AND DBMS_DB_VERSION.RELEASE < 2)
19 $THEN
20 v_pices := CEIL(LENGTHB(v_str)/C_NumOfChars);
21 FOR i IN 1..v_pices LOOP
22 DBMS_OUTPUT.PUT_LINE(SUBSTR(v_str,(i-1)*C_NumOfChars+1,C_NumOfChars));
23 END LOOP;
24 $ELSE
25 DBMS_OUTPUT.PUT_LINE(v_str);
26 $END
27* END;
SCOTT> /
プロシージャが作成されました。
SCOTT>Oracle10g R1環境で条件付きコンパイル後のコードを確認！
SCOTT> exec dbms_preprocessor.print_post_processed_source('PROCEDURE','SCOTT','DBMS_OUTPUT_TEST');
PROCEDURE dbms_output_test
(
i_str IN VARCHAR2
)
IS
v_str VARCHAR2(32767);
v_pices PLS_INTEGER;
C_NumOfChars CONSTANT PLS_INTEGER := 85;
BEGIN
v_str := i_str;
v_pices := CEIL(LENGTHB(v_str)/C_NumOfChars);
FOR i IN 1..v_pices LOOP
DBMS_OUTPUT.PUT_LINE(SUBSTR(v_str,(i-1)*C_NumOfChars+1,C_NumOfChars));
END LOOP;
END;
PL/SQLプロシージャが正常に完了しました。
SCOTT>
SCOTT> exec dbms_output_test(rpad('32767',32767,'*'));
32767********************************************************************************
*************************************************************************************
*************************************************************************************
・・・・・中略・・・・・
*************************************************************************************
*************************************************************************************
******************************************
PL/SQLプロシージャが正常に完了しました。
SCOTT>
● Oracle10g R2 10.2.0.1.0
Oracle10g R2ではPL/SQLの条件付きコンパイルは最初から有効になっている。
SCOTT> set serveroutput on size 100000
SCOTT> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SCOTT> l
1 CREATE OR REPLACE PROCEDURE dbms_output_test
2 (
3 i_str IN VARCHAR2
4 )
5 IS
6 v_str VARCHAR2(32767);
7 $IF DBMS_DB_VERSION.VERSION <= 9
8 OR (DBMS_DB_VERSION.VERSION = 10
9 AND DBMS_DB_VERSION.RELEASE < 2)
10 $THEN
11 v_pices PLS_INTEGER;
12 C_NumOfChars CONSTANT PLS_INTEGER := 85;
13 $END
14 BEGIN
15 v_str := i_str;
16 $IF DBMS_DB_VERSION.VERSION <= 9
17 OR (DBMS_DB_VERSION.VERSION = 10
18 AND DBMS_DB_VERSION.RELEASE < 2)
19 $THEN
20 v_pices := CEIL(LENGTHB(v_str)/C_NumOfChars);
21 FOR i IN 1..v_pices LOOP
22 DBMS_OUTPUT.PUT_LINE(SUBSTR(v_str,(i-1)*C_NumOfChars+1,C_NumOfChars));
23 END LOOP;
24 $ELSE
25 DBMS_OUTPUT.PUT_LINE(v_str);
26 $END
27* END;
SCOTT> /
プロシージャが作成されました。Oracle10g R2で条件コンパイル後のコードを確認！（コードがこんなにシンプルに！）
SCOTT> exec dbms_preprocessor.print_post_processed_source('PROCEDURE','SCOTT','DBMS_OUTPUT_TEST');
PROCEDURE dbms_output_test
(
i_str IN VARCHAR2
)
IS
v_str VARCHAR2(32767);
BEGIN
v_str := i_str;
DBMS_OUTPUT.PUT_LINE(v_str);
END;
PL/SQLプロシージャが正常に完了しました。
SCOTT> exec dbms_output_test(rpad('32767',32767,'*'));
32767***************************************************************************
********************************************************************************
********************************************************************************
********************************************************************************
・・・・・中略・・・・・
********************************************************************************
********************************************************************************
***********************************************
PL/SQLプロシージャが正常に完了しました。
SCOTT>
| 固定リンク | 0
コメント