Oracle-SQL长度限制

最近遇见一个ORA: maximum number of expressions in a list is 错误才知道Inlist有个元素的限制可以使用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

K maximum; particular tools may impose lower limits

i的文档写的最大K其实不正确

在g的文档中作了修改 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

l_stmt dbms_sqlvarchars;

l_cursor integer default dbms_sqlopen_cursor;

l_rows number default ;

l_length number := ;

begin

l_stmt() := select c;

for i in

loop

l_stmt(i) := c || i;

end loop;

l_stmt() := from dual;

for i in l_stmtfirst l_stmtlast

loop

l_length := l_length + length(l_stmt(i));

end loop;

dbms_outputput_line( length = || l_length );

dbms_sqlparse( c => l_cursor

statement => l_stmt

lb => l_stmtfirst

ub => l_stmtlast

lfflg => TRUE

language_flag => dbms_sqlnative );

l_rows := dbms_sqlexecute(l_cursor);

dbms_sqlclose_cursor( l_cursor );

end;

/

length =

PL/SQL procedure successfully completed

通过dbms_sqlvarchars数组可以执行很长的SQL

再将如上pl/sql产生的SQL使用dbms_output输出longSQLsql

SQL> host ls l longSQLsql

rwrr oracle dba Dec : longSQLsql

SQL> host head longSQLsql

select c

c

SQL> host tail longSQLsql

c

c

from dual;

这个M的SQL在sqlplus里完全可以执行看来oracle完全支持很长的SQL语句但该sql会占用很多share pool空间因此不提倡经常执行

SQL> select sql_text SHARABLE_MEMPERSISTENT_MEMRUNTIME_MEM from v$sql where sql_text like % c%;

SQL_TEXT

SHARABLE_MEM PERSISTENT_MEM RUNTIME_MEM

select c c c c c c c c c c

c c c c c c c c c