熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

Oracle-SQL長度限制

2013-11-13 15:53:38  來源: Oracle 

  最近遇見一個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
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.