Limitation in oracle db list query

[kishoreb]

I am getting the error while retrieving the records and exporting them to excel sheet where i had a query as follows

select * from FEEDBACK_IN Where feedback_in.case_id IN ( (param 1) , (param 2) , (param 3) , (param 4) , (param 5) , (param 6) , (param 7) , (param 8) , (param 9) , (param 10) , (param 11) , (param 12) , (param 13)……………………….(param 5000))

If the list exceeds the 1000 records the error occurs from the oracle database.

Is there any way to handle this situation to export the data to the excel sheet..

[Kronin555]

It's a limitation in Oracle. Google for error message ORA--01795

This link will give you a workaround:
http://www.dbforums.com/showthread.php?t=1004840&goto=nextoldest

Basically, you need to avoid sending IN lists that contain more than 1,000 items. You can do this one of two ways...

WHERE blah IN (1, 2, ..., 1000) OR blah IN (1001, 1002, ...., 2000) ...

-or-

(SELECT * FROM mytable WHERE blah IN (1, 2, ..., 1000))
UNION
(SELECT * FROM mytable WHERE blah IN (1001, 1002, ..., 2000))
...

[Dan_Bracuk]

There might be an easier way, depending on where your 5000 list items come from. If they come from a query, you can use queries of queries. If they come from cffile, you and use cfhttp to create a query and then use query of queries.

As it so happens, this very thing is a potential problem with my current project, and I'm hoping the QofQ approach will work.