By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,197 Members | 959 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,197 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
Jul 19 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.