The maximum number of expressions in a list in Oracle is 1000

is a limitation of oracle, and there are many ways to solve it.

For example, the ** of c below.

1. Write a method first and receive 2 parameters.

Parameter 1: Receive data in in, such as:'a1','a2',..'a2000' ；

Parameter 2: the name of the column that needs to be in;

public string getsqlin( string sqlparam, string columnname )

int width = "'", 1 ) 1;

string temp = ;

for( int i = 0; i < i += 1000 * width + 3 )

if( i + 1000 * width + 3 ) 1 <

temp = temp + i, 1000 * width + 3 ) 1 )

or " + columnname + " in (";

elsetemp = temp + i, -i );

return temp;

2. The return value of using this method, ** is as follows:

sql = new "");

select " );

from test t" );

where 1=1 " );

if( >0 )

string sqlstr = getsqlin( col, "colname" );

and in ( " + sqlstr + " )" );

order by " );

3. The format of the SQL string obtained after running is as follows:

select t.* from test t where in (59,60) or in (61,62)

This solves the problem that in is greater than 1000.

What if there are 10 tens of thousands of data?

Frightened, amazing, so far I haven't encountered a table with so many columns, so many column characters don't meet the requirements of the three normals of database design

1000 columns is not enough, what a big project you have.

The maximum length of the field name is 30 characters, and the maximum number of columns is 1000, so there seems to be no way to solve it.

It is 1000, please see oracle9i's virtual lead sql reference:

the absolute maximum number of columns in a table is 1000. however, when you

create an object table (or a relational table with columns of object, nested table,varray, or ref type), oracle maps the columns of the user-defined types to

relational columns, creating in effect "hidden columns" that count toward the

1000-column limit.

Is this a tip?

ora-01795: The maximum number of expressions in the list is 1000, what's your case?

Too many lists when writing in?

If so, you can split the value in and put it in the database table (temporary table), and then associate it.

In SQL, in is followed by a query statement, as long as it is a result set, for whether there is a number limit, but I really haven't encountered it, it is recommended that you write your parameters in the form of a select statement, and actually test it.

declare

i number;

r number;

str long := '';

beginfor i in 1 ..1000 loopstr := str ||i ||',';

r:=i;end loop;

execute immediate 'select 1 from dual where 1 in (' ||str||to_char(r+1) |')';

end;The maximum number of expressions in the list is 1000, otherwise the error message is displayed in oracle 10g: The maximum number of expressions in the list is 1000

The maximum number should not exceed 1,000, otherwise an error message is reported when the Oracle 10 GB message is generated.

ORA-01795: The maximum number of expressions in a list is 1000

How many hundreds of in() parameters? Are you sure you want to write out these hundreds of parameters one by one?

You should work around.

in(), which can also receive select query statements, such as select * from emp where deptno in (select deptno from dept).

There is a limit to the length of a SQL statement.

1. When constraining the table structure, the constraints generally established are system-defined constraints.

2. However, the constraints created in this way are not intuitive when viewing the constraint definition. And its constraint naming is also defined according to the system.

3. If there are multiple constraints in a table, it is difficult to distinguish which field the constraints are specified to. This is especially true when there are two identical constraints on a table.

4. Therefore, it is recommended to use the constraint keyword to create custom constraints when creating constraints. The constraints created in this way make it clear which field the constraints are acting on when you look at them.

5. But when customizing the constraint alias through constraint, keep in mind that the constraint constrains the alias; It should be followed by the field type, and cannot be written in other places, otherwise an error will be reported as shown in the following figure.

A in (1,2,.. can be solved simply like this999) or a in (1000,1001,..1999) or ..

Or store the in in a temporary table, and then associate the query! Such as:

with t as (

select 1 id from dualunion all

.select 199999 id from dual)select ..from table where a in (select * from t).

You write it one by one, then it's better to organize a table, write in the table, and use in (select table) to get affordable!

Give you a copy of a paragraph:

The project was used in oracle where

The in condition query statement in the statement. in(,, the number of data in parentheses is relatively large (here is the situation of regional query), and there are more than 7,000 districts, streets, and communities under Beijing. There are several ways to solve this problem.

1. Write a method to replace the condition with in(,,or in (,or in,)

Ensure that the number of data in each IN does not exceed 1,000. The problem is that the execution is inefficient when dealing with this. First of all, the query comes up with so many conditional values, and then there is that or in the query

Condition... 2. Establish an intermediate temp table with query conditions, and perform direct query in the database.

select * from table_1 where column_1 in ( select column_2 from

table_2_temp )

ps: I didn't use this way...

3. Abandon the kind of SQL statement that first finds out the query conditions, and then puts the conditions in the SQL statement of the query target result set, and writes SQL statements directly.

select * from table_1 ，。

where column_1 in ( select column_2 from table_2 ，。

While achieving business results, it also improves execution efficiency.

Think about what it would be like to execute an SQL statement for a few minutes...

sql> create table test3(name varchar2(4001));

create table test3(name varchar2(4001))

Error on line 1:

ora-00910: specified length is too long for data type sql> create table test3(name varchar2(4000));

A table has been created.