Improper result when number table is used to substitute the "IN" clause in oracle

From: sarith sutha <sarithsutha_at_indiatimes.com>
Date: 12 Feb 2004 08:23:20 -0800
Message-ID: <3df53150.0402120823.422d82e6_at_posting.google.com>

Hi Guys

Need a help i am facing a sporadic issue when executing the query using nested table of numbers

Here are the things which i did

1. I created a type as CREATE TYPE NUMBERTABLE AS TABLE OF NUMBER;

2.In the JDBC Code
i declare a long[] longArray and populate the long array by going through some query

3. Then i execute the following query
String str = "select id from table1 where id in (SELECT * FROM TABLE (SELECT CAST(? AS NUMBERTABLE ) FROM DUAL)) OraclePreparedStatement pstmt = conn.prepareStatement(str)

//Create the ArrayDescriptor and array object ArrayDescriptor desc = ArrayDescriptor.createDescriptor("NUMBERTABLE " ,conn);
ARRAY array_to_pass = new ARRAY(desc, conn, longArray ); pstmt.setARRAY(1,array_to_pass);

//Execute the Query

ResultSet rs = pstmt.executeQuery();

while(rs.next())
{

       System.out.println("**** Id is : " + rs.getLong(1)); }

4.Guys the problem i am facing is that query is not displaying all the results

For Ex:
if the longArray = {100,101,102,103,104} , if this array is bound and the query executed, sometimes not all the 5 values are displayed , like this one happened to me

"**** Id is : 100
"**** Id is : 101
"**** Id is : 102

103,104 were not displayed even though the longArray contained them ,Plz dont suggest me to use "IN" clause instead since the longarray size may exceed 1000 entries

Can anyone there tell why the last 2 entries are not displayed(chopped),guys my work is stuck up ,pl help

sarith Received on Thu Feb 12 2004 - 17:23:20 CET