In Oracle Database 11g, native dynamic SQL now supports statements bigger than 32K characters Tweet

久々にOracleネタ。

Oracle11gのPL/SQLのDynamic SQLでCLOBが使えるようになったようなので試してみた。
10gまではexecute immediate文で使えるのは最大でVARCHAR2(32767)までだったワケで、ど〜〜〜〜〜〜しても（書きたくないけど・・）巨大なSQL文をダイナミックに実行しなきゃいけない人には朗報？なんでしょうね。（DWH系ではCLOBが必要なくらい巨大なクエリをダイナミックに生成するなんてこともあるんでしょうね。私は今のところ３２Kを超えるクエリは書いたことないけどそれに近いのは過去１度あったかな？！　という程度。）

ということで10gと11g for linux(x86)を使って確認！（ClientはMacOSX(PPC)版Instant Clientですよん）

まず最初はOracle10g
execute immediate文に利用できるのはVARCHAR2(32767)が最大サイズなのでダイナミックに生成するSQL文もそのサイズに制限されちゃう。VARCHAR2(32767)を超えるとORA-06502が発生する。（尚、10gの場合でもDBMS_SQLを使えばもっと大きいサイズの動的SQLも使用できる。）

G5Server:˜ discus$ sqlplus /nolog

SQL*Plus: Release 10.1.0.3.0 - Production on 金 2月 6 1:19:22 2009

Copyright (c) 1982, 2004, Oracle.  All rights reserved.

> conn scott@catfish
パスワードを入力してください: 
接続されました。
SCOTT> set serveroutput on
SCOTT> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Prod
PL/SQL Release 10.1.0.3.0 - Production
CORE    10.1.0.3.0      Production
TNS for Linux Server: Version 10.1.0.3.0 - Production
NLSRTL Version 10.1.0.3.0 - Production

SCOTT> l
  1  CREATE OR REPLACE PROCEDURE dynamic_sql_test10g(iLength IN NUMBER)
  2  IS
  3    vSQL VARCHAR2(32767);
  4    vResult NUMBER;
  5  BEGIN
  6    vSQL := 'SELECT 1';
  7    FOR i IN 1.. iLength LOOP
  8      vSQL := vSQL || '+1';
  9    END LOOP;
 10    vSQL := vSQL || ' FROM DUAL';
 11    EXECUTE IMMEDIATE vSQL INTO vresult;
 12    DBMS_OUTPUT.PUT_LINE('Result:'||TO_CHAR(vresult));
 13* END;
SCOTT> /

プロシージャが作成されました。

SCOTT> exec dynamic_sql_test10g(16374);
Result:16375

PL/SQLプロシージャが正常に完了しました。

SCOTT> exec dynamic_sql_test10g(16375);
BEGIN dynamic_sql_test10g(16375); END;

*
行1でエラーが発生しました。:
ORA-06502: PL/SQL: 数値または値のエラー:
文字列バッファが小さすぎます。が発生しました ORA-06512:
"SCOTT.DYNAMIC_SQL_TEST10G", 行10
ORA-06512: 行1

SCOTT> 

11gの場合
execute immediate文にCLOBが使えるようになったことで以下のように３２Ｋ超えの動的SQLも実行できちゃうのだ。

注）
以下のコードで遊ぶときは個人で遊べるデータベースでやってくださいね。（本番データベースや多数の開発者で共有しているテスト／開発データベースでどとんでもなく巨大なクエリを実行すると他の方の迷惑になるので・・・・・）

SCOTT> 
SCOTT> conn scott@lampeye
パスワードを入力してください: 
接続されました。
SCOTT>
SCOTT> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE    11.1.0.6.0      Production
TNS for Linux: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

SCOTT> 
SCOTT> l
  1  CREATE OR REPLACE PROCEDURE dynamic_sql_test11g(iLength IN NUMBER)
  2  IS
  3    vSQL CLOB;
  4    vResult NUMBER;
  5  BEGIN
  6    vSQL := 'SELECT 1';
  7    FOR i IN 1.. iLength LOOP
  8      vSQL := vSQL || '+1';
  9    END LOOP;
 10    vSQL := vSQL || ' FROM DUAL';
 11    EXECUTE IMMEDIATE vSQL INTO vresult;
 12    DBMS_OUTPUT.PUT_LINE('Result:'||TO_CHAR(vresult));
 13* END;
SCOTT> /

プロシージャが作成されました。

SCOTT> set serveroutput on
SCOTT> exec dynamic_sql_test11g(32767);
Result:32768

PL/SQLプロシージャが正常に完了しました。

SCOTT> 
SCOTT> set timi on  
SCOTT> exec dynamic_sql_test11g(1000000);
Result:1000001

PL/SQLプロシージャが正常に完了しました。

経過: 00:06:25.93
SCOTT>