java ora 1000_java.sql。SQLSyntaxErrorException: ORA-01795:列表中表達式的最大數量是1000。...

we all know we see this exception when try to bomabard the query with more than 1000 values. the maximum for the column limit is 1000.. the best possible solution is to split the query into two.guys can u suggest some possible ways of code refactoring to make my problem go away.any help would be appreciated.

我們都知道，當嘗試使用超過1000個值的查詢時，我們會看到這個異常。列極限的最大值是1000。最好的解決方案是將查詢拆分為兩個。大家可以提出一些代碼重構的方法來解決我的問題。如有任何幫助，我們將不勝感激。

we see the exception when audTypeFieldIdList are greater than 1000 values.

當audTypeFieldIdList大於1000時，我們會看到異常。

try {

String query = "select AUDIT_TYPE_FIELD_ID, FIELD_NAME from AUDIT_TYPE_FIELD where AUDIT_TYPE_FIELD_ID in (";

int x = 0;

for (int y = 1; y <= audTypeFieldIdList.size(); y++) {

query += audTypeFieldIdList.get(x);

if (y != audTypeFieldIdList.size()) {

query += ", ";

}

x++;

}

query += ")";

List audTypeFieldIdList, Connection connection) {

ResultSet rs = null;

Statement stmt = null;

List audTypeFieldList = new ArrayList();

try {

String query = "select AUDIT_TYPE_FIELD_ID, FIELD_NAME from AUDIT_TYPE_FIELD where AUDIT_TYPE_FIELD_ID in (";

int x = 0;

for (int y = 1; y <= audTypeFieldIdList.size(); y++) {

query += audTypeFieldIdList.get(x);

if (y != audTypeFieldIdList.size()) {

query += ", ";

}

x++;

}

query += ")";

stmt = connection.createStatement();

rs = stmt.executeQuery(query);

while (rs != null && rs.next()) {

AuditTypeField audTypeField = PluginSystem.INSTANCE

.getPluginInjector().getInstance(AuditTypeField.class);

audTypeField.setId(rs.getLong("AUDIT_TYPE_FIELD_ID"));

audTypeField.setName(rs.getString("FIELD_NAME"));

audTypeFieldList.add(audTypeField);

}

return audTypeFieldList;

return audTypeFieldList;

1 个解决方案

#1

5

You can't use more than 1000 entries in IN clause. There are 2 solutions as mentioned below:

在從句中你不能使用超過1000個條目。有以下兩種解決方案:

Use inner query to solve this issue. You can create a temporary table and use that in your IN clause.

使用內部查詢來解決這個問題。您可以創建一個臨時表，並在您的子句中使用它。

Break it in the batch of 1000 entries using multiple IN clause separated by OR clause.

使用由OR子句分隔的多個in子句將其分解為1000個條目。

sample query:

查詢樣例:

select * from table_name

where

column_name in (V1,V2,V3,...V1000)

or

column_name in (V1001,V1002,V1003,...V2000)

...

閱讀更多…看看甲骨文常見問題解答