Study the problem of too long dynamic SQL statements in Oracle

The SQL statement is too long and needs to be stored through the array dbms_ SQL .varchar2s. Before execution.

Declare
Rochelle stmt dbms_ SQL .varchar2s;
Rochelle cursor integer default dbms_ SQL .open_cursor;
Rows number default 0;
Rochelle length number: = 0;
Begin
Rochelle stmt (1): = 'select 1 c1 ';
For I in 2 .. 15000 Loop
Rochelle stmt (I): = ', 1 C' | I;
End loop;
Rochelle stmt (15000): = 'from dual ';
For I in l_stmt.first... l_stmt.last
Loop
Rochelle length: = Rochelle Length + Length (Rochelle 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,
Export age_flag => dbms_ SQL .native );
Rows: = dbms_ SQL .execute (l_cursor );
Dbms_ SQL .close_cursor (l_cursor );
End;
/

See the following: http://www.stcore.com/html/2006/0307/114096.html

Recently met a ORA-01795: Maximum number of expressions in a list is 1000 error, only to know that in-list has a limit of 1000 elements; can be solved using array bind or temporary table, you can also bind variables. [Limit and conversion very long in list: Where X in (,...)]

I wonder if there are other restrictions on the length of SQL in sqlplus?

Logical database limits

SQL statement Length
Maximum length of statements
64 K maximum; particle tools may impose lower limits

The maximum write size of 9i documents is 64 KB, which is incorrect.

Modified the logical database limits in the 10 Gb document.

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

Think about it. There are a lot of packages in our database, which were originally created using sqlplus. sqlpplus does not have a limit on the length of the SQL statements executed in daily use.

Run dbms_ SQL to execute a long SQL statement.

SQL> declare
2 l_stmt dbms_ SQL .varchar2s;
Rochelle cursor integer default dbms_ SQL .open_cursor;
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 Rochelle 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 export age_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.

You can run a long SQL statement using the dbms_ SQL .varchar2s array.

Use dbms_output to output longsql. SQL.

SQL> host LS-l longsql. SQL
-RW-r -- 1 Oracle DBA 1215082 DEC 4 longsql. SQL

SQL> host Head longsql. SQL
Select 1 C1
, 1 C2
...........................

SQL> host tail longsql. SQL
................
, 1 c14999
, 1 c15000
From dual;

This 1 mb SQL statement can be executed in sqlplus. It seems that Oracle fully supports a long SQL statement, but this SQL occupies a lot of share pool space, so it is not recommended to execute it frequently.

SQL> select SQL _text, sharable_mem, persistent_mem, runtime_mem from V $ SQL where SQL _text like '%, 1 C2 % ';

SQL _text
partition
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 column, 1 column, 1 column, 1 column, 1 ....................................... ........................................ ..............
1978774 780448 241252