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
   

From:http://tw.wingwit.com/Article/program/Oracle/201311/17474.html