UKJA@ukja1021> 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 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
次のように4,000バイトはよく表現されます。
UKJA@ukja1021> select rpad('x',4000,'x') from dual;
RPAD('X',4000,'X')
--------------------------------------------------------------------------------
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
...
10,000バイトも使用できましょうか。次の結果を見ると、あら、支援されますよね。
UKJA@ukja1021> select rpad('x',10000,'x') from dual;
RPAD('X',10000,'X')
--------------------------------------------------------------------------------
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
...
でも、実際は4,000バイトで切られてしまったのが分かります。
UKJA@ukja1021> select length(rpad('x',10000,'x')) from dual;
LENGTH(RPAD('X',10000,'X'))
---------------------------
4000
次のような無理な試みはやっぱり駄目でしょう。
UKJA@ukja1021> select rpad('x',4000,'x')||rpad('x',4000,'x') from dual;
select rpad('x',4000,'x')||rpad('x',4000,'x') from dual
*
ERROR at line 1:
ORA-01489: result of string concatenation is too long
幸いにPL/SQL内ではVARCHAR2を32Kバイトまで使用できます。もちろんPL/SQL内でだけ使用可能です。このぐらいの長い文字列だったら大部分SQL文章を動的に生成しようとする場合です。
UKJA@ukja1021> declare
2 v_sql varchar2(32767);
3 v_cursor sys_refcursor;
4 begin
5 v_sql := 'select ';
6 for idx in 1 .. 100 loop
7 v_sql := v_sql || q'[']' || rpad('x',200) || q'[',]';
8 end loop;
9 v_sql := v_sql || ' 1 from dual';
10
11 open v_cursor for v_sql;
12 close v_cursor;
13 end loop;
14 /
PL/SQL procedure successfully completed.
でも32Kバイトを越えるとエラーが出ます。
UKJA@ukja1021> declare
2 v_sql varchar2(32767);
3 v_cursor sys_refcursor;
4 begin
5 v_sql := 'select ';
6 for idx in 1 .. 300 loop
7 v_sql := v_sql || q'[']' || rpad('x',200) || q'[',]';
8 end loop;
9 v_sql := v_sql || ' 1 from dual';
10
11 open v_cursor for v_sql;
12 close v_cursor;
13 end loop;
14 /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 7
こんな場合にはCLOBを使用すればいいです。でもCLOBの場合にはREF CURSORとは使用できません。
UKJA@ukja1021> declare
2 v_sql clob;
3 v_cursor sys_refcursor;
4 begin
5 v_sql := 'select ';
6 for idx in 1 .. 300 loop
7 v_sql := v_sql || q'[']' || rpad('x',200) || q'[',]';
8 end loop;
9 v_sql := v_sql || ' 1 from dual';
10
11 open v_cursor for v_sql;
12 close v_cursor;
13 end loop;
14 /
open v_cursor for v_sql;
*
ERROR at line 11:
ORA-06550: line 11, column 20:
PLS-00382: expression is of wrong type
ORA-06550: line 11, column 2:
PL/SQL: Statement ignored
当然にEXECUTE IMMEDIATEでも願うとおり動作しません。では32Kバイトより長いSQL文章を動的に使用できる方法は何でしょうか。次のように少しは複雑に見える方法でDBMS_SQLパッケージを使用することです。CLOBをいくつかの小さなVARCHAR2文字列に割ってDBMS_SQL.PARSEパッケージに伝達します。
UKJA@ukja1021> declare
2 v_sql clob;
3 v_array dbms_sql.varchar2a;
4 v_curno number;
5 v_ret integer;
6 v_ub number;
7 begin
8 v_sql := 'select ';
9 for idx in 1 .. 300 loop
10 v_sql := v_sql || q'[']' || rpad('x',200) || q'[',]';
11 end loop;
12 v_sql := v_sql || ' 1 from dual';
13
14 v_curno := dbms_sql.open_cursor;
15 v_ub := ceil(dbms_lob.getlength(v_sql)/1000);
16 for idx in 1 .. v_ub loop
17 v_array(idx) := dbms_lob.substr(v_sql, 1000, (idx-1)*1000+1);
18 end loop;
19
20 dbms_sql.parse(v_curno, v_array, 1, v_ub, false, dbms_sql.native);
21 v_ret := dbms_sql.execute(v_curno);
22 dbms_sql.close_cursor(v_curno);
23 end loop;
24 /
PL/SQL procedure successfully completed.
幸いにOracle11gからはCLOBをREF CURSORに直接使用できます。EXECUTE IMMEDIATEでも使用できるのは当然ですよ。
UKJA@ukja1106> 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 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production
UKJA@ukja1106> declare
2 v_sql clob;
3 v_cursor sys_refcursor;
4 begin
5 v_sql := 'select ';
6 for idx in 1 .. 300 loop
7 v_sql := v_sql || q'[']' || rpad('x',200) || q'[',]';
8 end loop;
9 v_sql := v_sql || ' 1 from dual';
10
11 open v_cursor for v_sql;
12 close v_cursor;
13 end loop;
14 /
PL/SQL procedure successfully completed.
DBMS_SQLパッケージでもCLOBをSQLテキストに使用可能です。
UKJA@ukja1106> declare
2 v_sql clob;
3 v_curno number;
4 v_ret integer;
5 begin
6 v_sql := 'select ';
7 for idx in 1 .. 300 loop
8 v_sql := v_sql || q'[']' || rpad('x',200) || q'[',]';
9 end loop;
10 v_sql := v_sql || ' 1 from dual';
11
12 v_curno := dbms_sql.open_cursor;
13 dbms_sql.parse(v_curno, v_sql, dbms_sql.native);
14 v_ret := dbms_sql.execute(v_curno);
15 dbms_sql.close_cursor(v_curno);
16 end loop;
17 /
PL/SQL procedure successfully completed.
なお、REF CURSORとDBMS_SQLの間に変換も自由です。以前の制約がほとんどなくなりました。
UKJA@ukja1106> declare
2 v_sql clob;
3 v_curno number;
4 v_ret integer;
5 v_cursor sys_refcursor;
6 begin
7 v_sql := 'select ';
8 for idx in 1 .. 300 loop
9 v_sql := v_sql || q'[']' || rpad('x',200) || q'[',]';
10 end loop;
11 v_sql := v_sql || ' 1 from dual';
12
13 v_curno := dbms_sql.open_cursor;
14 dbms_sql.parse(v_curno, v_sql, dbms_sql.native);
15 v_ret := dbms_sql.execute(v_curno);
16 v_cursor := dbms_sql.to_refcursor(v_curno);
17 close v_cursor;
18
19 end loop;
20 /
PL/SQL procedure successfully completed.
やはり新しいバージョンがいいですね。
Posts
No comments:
Post a Comment