In this section we will see about how to handle IN clause limitation exceeding > 1000 values(ORA-01795 Error) in oracle.
Consider the following query
The above query will return results containing for the given author names in where clause. But there is a limitation that the IN clause cannot have more than 1000 values. Mostly the work around will be to loop through the select statement for 10 times if there are 10000 values.
Now we will discuss about two more solutions better than the work around.
#1 Using Oracle Object Type Table
Steps to be followed:
i) Create a String Array with In clause values, lets say strArray
ii) Create a Oracle Object Type table in Oracle
CREATE TYPE INCLAUSETABLE AS TABLE OF VARCHAR2(1000);
iii) Create a ARRAY DESCRIPTOR in java code similar to this
ArrayDescriptor desc = ArrayDescriptor.createDescriptor("INCLAUSETABLE", conn);
iv) Set the String Array constructed to the Array descriptor
ARRAY array_to_pass = new ARRAY(desc, conn, strArray);
v) Create a table TEST_IN_CLAUSE.
Create table TEST_IN_CLAUSE (name varchar2(100));
vi) Create a prepare statement with In clause as Oracle type
OraclePreparedStatement pstat =(OraclePreparedStatement) conn.prepareStatement("SELECT NAME FROM test_in " +
"WHERE name IN (SELECT * FROM TABLE (SELECT CAST(? AS INCLAUSETABLE) FROM DUAL)) ");
vii) Set the Array String and execute the Query
pstat.setARRAY(1, array_to_pass);
OracleResultSet rset = (OracleResultSet) pstat.executeQuery();
Have a look at the complete source code below.
ArrayINClauseTest Class
* THIS SOFTWARE IS PROVIDED BY THE AUTHOR AND CONTRIBUTORS ``AS IS''
* AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED
* TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR
* PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE AUTHOR OR CONTRIBUTORS
* BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR
* CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF
* SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS
* INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN
* CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE)
* ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
* POSSIBILITY OF SUCH DAMAGE.
*/
import java.sql.*;
import oracle.sql.*;
import oracle.jdbc.*;
public class ArrayINClauseTest
{
public static void main(String[] args) throws SQLException
{
String strArray[] = new String[100000];
System.out.println("Starting ...");
DriverManager.registerDriver(new OracleDriver());
Connection conn = DriverManager.getConnection(
"jdbc:oracle:thin:@<host_name>:<port_name>:<db_service_name>",
"<user_name>", "<pass_word>");
PreparedStatement pst = conn
.prepareStatement("INSERT into TEST_IN_CLAUSE(name)values(?)");
System.out.println(" Value of strArray.length " + strArray.length);
for (int i = 0; i < 5000; i++)
{
strArray[i] = "Test" + i;
pst.setString(1, strArray[i]);
pst.addBatch();
}
pst.executeBatch();
pst.close();
System.out.println(" Value of strArray.value 4999 " + strArray[4999]);
ArrayDescriptor desc = ArrayDescriptor.createDescriptor(
"INCLAUSETABLE", conn);
ARRAY array_to_pass = new ARRAY(desc, conn, strArray);
OraclePreparedStatement pstat = (OraclePreparedStatement) conn
.prepareStatement("SELECT NAME FROM TEST_IN_CLAUSE "
+ "WHERE name IN (SELECT * FROM TABLE (SELECT CAST(? AS INCLAUSETABLE) FROM DUAL)) ");
pstat.setARRAY(1, array_to_pass);
OracleResultSet rset = (OracleResultSet) pstat.executeQuery();
while (rset.next())
{
System.out.println(rset.getString(1));
}
pstat.close();
conn.close();
System.out.println("Finished ...");
}
}
The above code creates an array of 5000 strings and then inserts them into TEST_IN_CLAUSE table. Then we use oracle.sql.ARRAY and oracle.sql.ArrayDescriptor api classes to convert the java array in to an ARRAY of oracle object type and then we use that object type array to select from the in clause .
#2 Inserting in Global Temporary table and using that table in clause
i)First we need to Insert the list of values to be bonded in the IN clause in to a global temporary table, say table name is GLB_TEMP_TABLE.
create global temporary table GLB_TEMP_TABLE (name varchar2(1000))on commit delete rows;
ii) Then we can simply select it by using global temporary table in inclause .
select * from t where name in ( select name from GLB_TEMP_TABLE);