I mean where do use use such a query with 1000 entries in “IN” clause … what business logic is under neath … I cannot imagine most complex systems with such a db design … or logic.
So i wonder if you can explain the scenario, env, where you need such a thing.
Brg
Damir
Oh ok.
For example, the database has over 2 millions identiers. I have a specific sample (a few thousand) for example and i need to pull up all the deatils on those few thousand. I query security identifiers from a table in a database to get all the details about them from another table.
I can list them using OR statements, or I can use IN statements (1,000 at a time).
I find that “where” part as something on pk (or at least UQX column). If not you’ll suffer performance problem …
But OK, let’s leave that to other occasion. Let me show you different approach with unlimited IN solution.
SELECT JOB
FROM EMP WHERE JOB IN (:JOB1, :JOB2, :JOB3, ..., :JOB9999999999) <- not possible with "IN", but let assume there are more then 1000.
replace with:
SELECT a.job
FROM EMP a, (SELECT :JOB1 AS col FROM DUAL
UNION ALL
SELECT :JOB2 FROM DUAL
UNION ALL
SELECT :JOB3 FROM DUAL
UNION ALL
...
UNION ALL
SELECT :JOB9999999999 FROM DUAL) b
WHERE b.col = a.job
This is very easy to construct a procedure which will reproduce that code or manually fill, but as I mention no limitation
Brg
Damir Vadas
thanks, i’ll try that
Evening all,
There is indeed a hard coded limit of 1,000 literals, note that word, in an IN clause.
There is not a limit if you don't use literals.
If you can code something like:
WHERE X IN (SELECT Y FROM TABLE_Y WHERE . . .)
Oracle no longer limits you to 1,000.
I am not aware of any limits of 5,000 (total) literals in all the IN clauses of a statement though.
Maybe you are hitting a limit on the number of characters allowed, by Oracle, in a single statement.
I doubt that Toad is limiting you, nor do I think your company would be either.
HTH.
Norm. [TeamT]
Apologies for top posting, brevity and/or autocorrect mis-corrections, I am sending this from my Galaxy tablet, from the bath!
On March 13, 2014 3:26:24 PM GMT, orange99 bounce-orange99@toadworld.com wrote:
Reply by orange99
Bert,
I have done that in the past.
SELECT a., b.
FROM table1 a, table2 b
WHERE a.type=b.type
AND ((A.issuer_id_c = '62931Z' and A.issue_alpha_c IN ('NA','NB','NC','ND','NE', ... up to 1000)
OR (A.issuer_id_c = '145258' and A.issue_alpha_c IN ('NA','NB','NC','ND','NE')
OR (A.issuer_id_c = '685774' and A.issue_alpha_c IN ('NA','NB','NC','ND','NE')
OR (A.issuer_id_c = '469807' and A.issue_alpha_c IN ('NA','NB','NC','ND','NE')
OR (A.issuer_id_c = '778910' and A.issue_alpha_c IN ('NA','NB','NC','ND','NE'))
And it only lets me do 5 IN statements (each with up to 1000 id's) for a total of 5,000. Is this a Toad limit or is my company's system preventing me from doing this? I would like to be able to query up to 20k.
Thanks for the help.
To reply, please reply-all to this email.
Stop receiving emails on this subject.
Or Unsubscribe from Toad for Oracle - General notifications altogether.
Toad for Oracle - Discussion Forum
Flag this post as spam/abuse.
--
Sent from my Android device with K-9 Mail. Please excuse my brevity.
Maybe you are hitting a Windows resources or memory limit? What does Task Manager say you have left when Toad crashes out on a big query in the editor?
Alternatively, if you close everything down excpt toad, can you get the big query to run?
Cheers,
Norm. [TeamT]
Previous apology applies here too! 
On March 13, 2014 6:07:37 PM GMT, orange99 bounce-orange99@toadworld.com wrote:
Reply by orange99
John,
No error dialg window. The application just shuts off. One minute it's running the query, next minute application is closed.
Are you using Toad Base Edition or Pro?
thanks
To reply, please reply-all to this email.
Stop receiving emails on this subject.
Or Unsubscribe from Toad for Oracle - General notifications altogether.
Toad for Oracle - Discussion Forum
Flag this post as spam/abuse.
--
Sent from my Android device with K-9 Mail. Please excuse my brevity.
I can reproduce the disappearing Toad problem in 11.0 with a query like this. I just keep pasting in more and more of those “or” lines. When I get into the 5000 range, as you did, Toad disappears. I don’t think there is anything that I can do about it. In Toad 12.1 I was able to go 9000 without Toad crashing (but it did get very slow). Ultimately, I think the solution to your problem is to find a better way to write this query. If nothing else, you can insert the value pairs of interest into a separate table and then join to it (as I described early on)
Giant queries will cause problems eventually one way or another.
select * from dba_objects a
where 1=1
and ((A.OBJECT_NAME = ‘62931Z’ and A.OBJECT_TYPE = ‘NB’)
OR (A.OBJECT_NAME = ‘62931Z’ and A.OBJECT_TYPE = ‘NB’)
OR (A.OBJECT_NAME = ‘62931Z’ and A.OBJECT_TYPE = ‘NE’)
);
Ok, this could be fun! 
In the list of identifiers in your sample, is it a hard coded list written down, or is there a way to select the desired identifiers from the table they are found in?
In the latter case, do this:
SELECT STUFF FROM OTHER_TABLE
WHERE ID IN (SELECT ID FROM FIRST_TABLE WHERE . . .)
ORDER BY . . . ;
In the former case, type them into an editor, one per line, and use a macro to convert your list into:
With MyList as (
SELECT 12345 AS ID FROM DUAL UNION ALL
SELECT 23456 AS ID FROM DUAL UNION ALL
. . .
. . .
SELECT 98765 AS ID FROM DUAL
)
SELECT STUFF FROM OTHER_TABLE
WHERE ID IN (SELECT ID FROM MYLIST)
ORDER BY . . . ;
I have done that sort of thing many times myself.
HTH
Cheers,
Norm. [TeamT]
Previous apology still applies¡
On March 13, 2014 7:14:37 PM GMT, orange99 bounce-orange99@toadworld.com wrote:
Reply by orange99
Oh ok.
For example, the database has over 2 millions identiers. I have a specific sample (a few thousand) for example and i need to pull up all the deatils on those few thousand. I query security identifiers from a table in a database to get all the details about them from another table.
I can list them using OR statements, or I can use IN statements (1,000 at a time).
To reply, please reply-all to this email.
Stop receiving emails on this subject.
Or Unsubscribe from Toad for Oracle - General notifications altogether.
Toad for Oracle - Discussion Forum
Flag this post as spam/abuse.
--
Sent from my Android device with K-9 Mail. Please excuse my brevity.
Thanks for for your help. I will try your suggestions and let you know what I get.
I’m just loud thinking … have you tried the same sql in pure Oracle SqlPlus?
Do you have an error there? The same?
If, no error then Toad blame. If same error not Toad blame…very easy.
And this thread is pretty similar one with 1000 IN problem … where you got the solution in at least three ways.
