研究Oracle中動態SQL語句太長的問題

SQL語句太長了，需要通過dbms_sql.varchar2s這個數群組類型來進行儲存。然後才能執行。

declare
    l_stmt dbms_sql.varchar2s;
    l_cursor integer default dbms_sql.open_cursor;
    l_rows number default 0;
    l_length number := 0;
begin
    l_stmt(1) := 'select 1 c1';
    for i in 2 .. 15000 loop
        l_stmt(i) := ', 1 c' || i;
    end loop;
    l_stmt(15000) := 'from dual';
    for i in l_stmt.first .. l_stmt.last
    loop
        l_length := l_length + length(l_stmt(i));
    end loop;
    dbms_output.put_line( 'length = ' || l_length );
    dbms_sql.parse( c => l_cursor,
    statement => l_stmt,
    lb => l_stmt.first,
    ub => l_stmt.last,
    lfflg => TRUE,
    language_flag => dbms_sql.native );
    l_rows := dbms_sql.execute(l_cursor);
    dbms_sql.close_cursor( l_cursor );
end;
/

以下參考：http://www.stcore.com/html/2006/0307/114096.html

最近遇見一個ORA-01795: maximum number of expressions in a list is 1000 錯誤，才知道In-list有1000個元素的限制；可以使用array bind或者temporary table解決，而且還能綁定變數。[Limit and conversion very long IN list : WHERE x IN ( ,,, ...) ]

再想到SQL的長度是否在SQLPLUS裡面還有其他的限制？

Logical Database Limits

SQL Statement Length
 Maximum length of statements
 64 K maximum; particular tools may impose lower limits

9i的文檔寫的最大64K，其實不正確。

在10g的文檔中作了修改 Logical Database Limits

The limit on how long a SQL statement can be depends on many factors, including database configuration, disk space, and memory

仔細想想，我們資料庫中 package 幾十K有很多，當初都是用sqlplus執行建立的；sqlpplus對日常執行的SQL因該不會有長度限制。

先測試一下使用DBMS_SQL執行很長的SQL語句。

SQL> declare
2 l_stmt dbms_sql.varchar2s;
l_cursor integer default dbms_sql.open_cursor;
l_rows number default 0;
3 4 5 l_length number := 0;
6 begin
7 l_stmt(1) := 'select 1 c1';
8 for i in 2 .. 15000
9 loop
10 l_stmt(i) := ', 1 c' || i;
11 end loop;
12 l_stmt(15000) := 'from dual';
13 for i in l_stmt.first .. l_stmt.last
14 loop
15 l_length := l_length + length(l_stmt(i));
16 end loop;
17 dbms_output.put_line( 'length = ' || l_length );
18 dbms_sql.parse( c => l_cursor,
19 statement => l_stmt,
20 lb => l_stmt.first,
21 ub => l_stmt.last,
22 lfflg => TRUE,
23 language_flag => dbms_sql.native );
25 l_rows := dbms_sql.execute(l_cursor);
26 dbms_sql.close_cursor( l_cursor );
27 end;
28 /
length = 138898

PL/SQL procedure successfully completed.

通過dbms_sql.varchar2s數組，可以執行很長的SQL。

再將如上pl/sql產生的SQL使用dbms_output輸出longSQL.sql.

SQL> host ls -l longSQL.sql
-rw-r--r-- 1 oracle dba 1215082 Dec 4 05:19 longSQL.sql

SQL> host head longSQL.sql
select 1 c1
, 1 c2
...........................

SQL> host tail longSQL.sql
................
, 1 c14999
, 1 c15000
from dual;

這個1M的SQL在sqlplus裡完全可以執行。看來，oracle完全支援很長的SQL語句；但該sql會佔用很多share pool空間,因此不提倡經常執行。

SQL> select sql_text, SHARABLE_MEM,PERSISTENT_MEM,RUNTIME_MEM from v$sql where sql_text like '%, 1 c2%';

SQL_TEXT
--------------------------------------------------------------------------
SHARABLE_MEM PERSISTENT_MEM RUNTIME_MEM
------------ -------------- -----------
select 1 c1 , 1 c2 , 1 c3 , 1 c4 , 1 c5 , 1 c6 , 1 c7 , 1 c8 , 1 c9 , 1 c10 , 1
c11 , 1 c12 , 1 c13 , 1 c14 , 1 c15 , 1 c16 , 1 c17 , 1 c18 , 1 c19 , 1 , 1 .............................................................................................
1978774 780448 241252