How to pass more than 1000 parameters in SQL IN clause

Sorry! Just read the question completely.. Not possible to create temporary tables..
Other option is to make use of OR statement..

select * from ap_invoices_all
 where
invoice_num in (--less than 1000 values---)
OR invoice_num in (--less than 1000 values---)
OR invoice_num in (--less than 1000 values---)

I am not sure if the performance is going to be good for this. You might to have to check it out (performance might not be a problem based on the actual number of records)

you really should consider creating a table to write your values in
presumably the user will be selecting the invoices from a list
you probably populate this list from all the available invoices use a text and id from the invoice to identify it

the best way is write this selection a separate table
the table would be best user based
create table ap_invoices_selected as
  (invoice_num number(11,0) not null, -- datatype of invoice_num
   user_id varchar(100, 0) not null); -- identification of logged in user
you then fill up the selection table with the invoices selected and user id

and then use the query

select * from ap_invoices_all
 where invoice_num in (select invoice_num from ap_invoices_selected where user_id = 'x')

this way there is no limit to (except the diskspace/tablespace) number of invoices

if you only have view rights, then find somebody who has create table privilige
a database change should allways be considered a option for program changes

 Dear YYatin,
   
    While UNION can be used in simple queries like I have mentioned, It wold be cumbersome to use in case of queries which are bigger and more complex.

  If you have any other ideas than it would be more helpful

 Dear Geert_Gruwez

    Please suggest a solution which does not require the creation of a table.

inform your DBA person and create global temporaty table  gt_invoice_num and insert the all invoice number or u can use OR clause instead of In but it will effected your performance in query



select * from ap_invoices_all, gt_invoice_num
 where invoice_num = inv_num

i don't have any good ideas other than that

unless you have other criteria on what to select your invoices
i don't know many users who like selecting 4000 or 5000 invoices from a list and then getting the data

let's say they have a app which can show this list,
let the user select 1 item per sec (not due to program speed but user speed (mouse selecting))
= 5000 sec = 83min
and then starts the loading
and then sees a error
and then has to start all over ...

not very user friendly

can't you use date ranges or filter based on customer id or anything like that ?
i can't imagine any user dumb enough to be wanting to do it this way ...

 Dear Geert_Gruwez,

    If you don't have the soltion. Let's live with it. However this does not make the user dumb as there could be innumberable possibilities for why this is required.

 Dear Geert_Gruwez'

    It does not work like you have written. This does not take 83 minutes. It only takes 83 seconds. All the values are simply copied and pasted from excel and query executed.

okay,
i may have mistyped something there.

The key factor is how the selection of invoices happens.
Are these lines separate one selected with a combination of Ctrl-Click, Shift-Click
or do they select from line X to line Y
how is the sorting on these invoices
are they sorted by date

for example it the data were sorted by date
wouldn't it be simpler to select by date between start and end ?
you would only need to pass 2 variables ...

if the selection is by individual lines then this problem will off course persist
and will allways be performance issues

since you can't pass so many parameters
maybe you should make less parameters like this

assume first you would have 10 parameters of number type:

select * from ap_invoices_all
 where invoice_num in (:p001, :p002, :p003, :p004, :p005, :p006, :p007, :p008, :p009, :p010);

assuming value for :p001 to :p010
:p001 = 100
:p002 = 200
:p003 = 300
:p004 = 400
:p005 = 500
:p006 = 600
:p007 = 700
:p008 = 800
:p009 = 900
:p010 = 1100

you can change your query like this

select * from ap_invoices_all
 where instr(:px001, '-' || invoice_num || '-') > 0;

the variable would be like this:
:px001 = '-100-200-300-400-500-600-700-800-900-1000-1100-';

off course you'll need to convert the invoicenums to a string
this is a way of reducing your parameters

I agree with Geert. If the report needs 4000-5000 user inputs from a front-end, it is time for a re-design.

>>
the variable would be like this:
:px001 = '-100-200-300-400-500-600-700-800-900-1000-1100-';
<<

Quick note here: while this method is indeed clever, it probably would not work for invoice numbers unless they're really short, due to the 4k character limit in SQL varchar2 column. For example, if there are 1000 invoice numbers and you account for the separator ("-") that means you could only have invoices with average length of 3 bytes.

>>gatorvip
i was surprised it even passed with 999 parameters ...
999*3 + 998*',' + length('select * from ap_invoices_all where invoice_num in') > 4k

so the limitations is not 1000 parameters but in actual fact, the 4k boundary ...

conclusion : it will never work, no matter how you format the query

unless: re-design as mrjoltcola suggested

anyway
if somebody would hit any of the oracle databases i administer with a query like this,
they'd get kicked out

Often we see these types of questions, in short:

1) Programmer has read-only privileges
2) Programmer is stuck with bad design
3) Programmer is asking how to workaround bad design

The short answer is to tell the DBA the design is bad, and get him involved in the solution!

I have never asked my programmers to fix my design mistakes on their own.

But if you are not the DBA, or the system has moved to production mode and has frozen, then the points above may not help you with the short-term solution.

If you cannot change the core data model, then the practical solution here has already been stated above: write all of the values into a temp table and when the user clicks "Submit" or "Ok" join the temp table against the target table and Oracle will perform a join as efficiently as possible. Convince the DBA to grant you write privs to your own schema, use that schema user for the temp table, and do the cross-schema join at runtime.

Otherwise, tell your company / DBA that the cost of a programmer only solution to this problem is way more expensive then getting the DBA involved to do it right.

 Geert_Gruwez
   
     If you are an MIS person in place of database administrator than first yu will get kicked out if you are not prepared to comply with different information requirements.

lol,
the one query would probably cost a lot to maintain
and would also cause some performance issues

the one thing you need to do is find the dba and explain your problem
show the solution for your 1000+ parameters
the dba will jump to give you a better solution

if the dba doesn't jump then he/she doesn't mind a bad performing database

i'm affraid that this solution will never give optimal results for the end user

i got (virtually speaking) whacked on my fingers too last week by our senior dba
i was in the top 5 of bad performing queries
i do both sides (programming and dba), 1 foot in either job
it is allways a joint effort to get the best out of a program, both code and queries
i learned a lot about tuning and we both came to the conclusion that the basic problem is not the code, not the queries, but the whole way the system is operating
we need a completer redesign

so i can imagine completely what problems can arise

it is allways in the best interest of everyone to build as good an app as it can be, providing the design of the system is good enough

 I think the only feasible solution for this is to use the sub query for passing more than 1000 parameters. The limitation of 1000 parameters is not applicable if the data comes through a sub query.

 Thanks to all the experts who helped in finding the solution.

How do you intend to build the subquery?

From a design perspective, why does the app need 1000 parameters? Is this a GUI where the user selects many rows, page at a time, and then submits? If so, then after each select you could post the value to the database into a temporary table, then use the subquery when you finally submit.

this requirement is not for the creation of a regular report.

Sometimes for one time reporting requirements instead of creating a totally new report which may also result into complexities of creating joins and testing the data it would be better and muh faster to use the output from one query and pass it as an input into another query. This may also give faster results

I agree, but the question was regarding passing the parameters from the user level to the database. Somehow I lost track of how we transitioned from that to having them in the database already. I agree, its a better solution, but it was not clear how you solved the original problem of collecting the values that were originally passed in an IN() clause. If you are happy with the suggestions, great, no need to discuss more, but I asked for clarification for the sake of future readers.

 We are using the application Toad for oracle where we get the viewer rights only, so that the user can retrieve one time data requirements.

So as an end user we can also retrieve data from back end. We can run queries in toad. Now you would have got the requirement of passing so many parameters. Usually we may get the data out of one query, put it in excel and join the parameters through concatenate function. Suppose we get the output from first query as follows : 25,35,40,50,60.

Now using excel concatenate function we will convert the data into : '25','35','40','50','60'. Now this set of data can be pasted in second toad query and the data can be retrieved from it.

This may be lot easier than creating joins between the two queries. Sometimes joining them may also result into inaccurate data.

Passing the data of one query in another query results into faster execution also. Even using the sub query will affect the performance a lot.

This type of back end data retrieval is necessary where you need to get one set of data out of several modules like payables.purchasing fixed assets etc. and no standard report is available.

Any further suggestions and clarifications are most welcome.