StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p><strong>22.</strong></p> <p>Well, not exactly. That's how many OR clauses of 1000-item IN lists will run on <em>my</em> system, but that number will probably be different for everyone. There is no <a href="http://docs.oracle.com/cd/E16655_01/server.121/e17615/refrn0043.htm" rel="nofollow">database limit</a> that exactly covers this scenario. It probably falls under the Note:</p> <blockquote> <p>The limit on how long a SQL statement can be depends on many factors, including database configuration, disk space, and memory</p> </blockquote> <p>When I try 23, I get this error in SQL*Plus:</p> <pre><code>ERROR at line 1: ORA-03113: end-of-file on communication channel Process ID: 2452 Session ID: 135 Serial number: 165 </code></pre> <p>Which is not the real error, that just means the server crashed and SQL*Plus lost its connection. Oddly, when I look in the alert log there are no errors. There are trace files but still no ORA- error messages. All I see is hundreds of lines like this:</p> <pre><code>*** 2013-11-04 21:59:48.667 minact-scn master-status: grec-scn:0x0000.00821c54 gmin-scn:0x0000.0081d656 gcalc-scn:0x0000.00821c54 minact-scn master-status: grec-scn:0x0000.00823b45 gmin-scn:0x0000.0081d656 gcalc-scn:0x0000.00823b46 </code></pre> <p>The lesson here is to avoid ridiculously large SQL statements. You'll have to do it another way, like loading the data into a table. And don't try to build something that is <em>just</em> small enough. It may work today but fail on a different environment tomorrow.</p> <hr> <pre><code>--Find the maximum number of IN conditions with 1000 items. --Change the first number until it throws an error. --This code uses dynamic SQL, but I found that static SQL has the same limit. declare c_number_of_ors number := 22; v_in_sql varchar2(4000); v_sql clob; v_count number; begin --Comma-separate list of 1000 numbers. select listagg(level, ',') within group (order by 1) into v_in_sql from dual connect by level <= 1000; --Start the statement. v_sql := 'select count(*) from dual '; v_sql := v_sql || 'where 1 in ('||v_in_sql||')'; --Append more ORs to it. for i in 1 .. c_number_of_ors loop v_sql := v_sql || ' or '||to_char(i)||' in ('||v_in_sql||')'; end loop; --Execute it. execute immediate v_sql into v_count; end; / </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload