0

I am using sql developer on an oracle database. My requirement is to pull out more than 8000 rows at once using primary key ID's that I have. sql developer doesn't allow me to fetch more than 1000 at once. All I can do is add an OR and pull a maximum of 2000(1000+1000). Is there a better way to pull ALL at once?

If a temporary table needs to be used, how do i go about it in oracle?

Select * from X where ID in (1,2,...1000) OR ID IN (1001,1002,...2000);

Thank you

Naresh Aligeti
  • 45
  • 2
  • 9
  • Do you have to use `IN`? Why not `BETWEEN 0 AND 8000` – Tikkes Apr 08 '13 at 09:53
  • Where do the IDs come from - another table? Passed in to a script or procedure? 8000 is a lot of values to manage and maintain manually. If they are contiguous then you could user `between` but it's not clear if the values you give are just examples? – Alex Poole Apr 08 '13 at 09:54
  • as I can still remember we use the IN keyword before but you should beware of that since it has maximum number of arguements – Jobert Enamno Apr 08 '13 at 10:03
  • Tikkes,I cannot use BETWEEN because my Primary key ID's are not a range. They do not follow a series pattern. – Naresh Aligeti Apr 08 '13 at 10:10
  • 1
    "sql developer doesn't allow me to fetch more than 1000 at once." NO. Sql develper alows you to fetch as many rows you want, as far as you have memory. You can fectch milions of rows. – Florin Ghita Apr 08 '13 at 10:10
  • Alex, I don't know where the primary ID's come from. I just get them from another person 3 times a week. Some times, I get around 15000 ID's. I pull 2000 values at a time in sets. – Naresh Aligeti Apr 08 '13 at 10:12
  • Florin Ghita, I am sure it would fetch more than a million rows. I have around 8000 primary id's. Can you tell me how do use them in a table to fetch related attributes ALL at ONCE? – Naresh Aligeti Apr 08 '13 at 10:15
  • You 'just get them' in what form? A file that could be put on the server and used as an external table? A CSV but you have no server access? You might be able to generate the text for a CTE from a file if necessary and use that as the equivalent of a 'temporary table', or you might need a global temporary table, which you then need to figure out how to populate, for example. I think we need to know what you're working with. – Alex Poole Apr 08 '13 at 10:32
  • possible duplicate of [How to put more than 1000 values into an Oracle IN clause](http://stackoverflow.com/questions/400255/how-to-put-more-than-1000-values-into-an-oracle-in-clause) or [Here](http://stackoverflow.com/questions/4722220/sql-in-clause-1000-item-limit) – A.B.Cade Apr 08 '13 at 11:08

1 Answers1

3

Put all of the ID in a temp table.

Then perform the SELECT query WHERE id IN (SELECT id FROM < the temp table you created>).

Should work.

e.g. of TEMP TABLE creation in Oracle:

CREATE TABLE `temp`
   AS SELECT distinct `id` FROM `<your table>`;

Cheers.

d'alar'cop
  • 2,357
  • 1
  • 14
  • 18
  • 1. How do I create a temp table? 2. Do i use a sql JOIN to join temp table and the table of interest and then use the SELECT statement. I am sorry, I am getting confused. – Naresh Aligeti Apr 08 '13 at 10:22
  • `create table #t(id int)` – Prahalad Gaggar Apr 08 '13 at 10:24
  • yep Luv is right - but there is a shorthand creation/population which i put in the main body ^^^^^^ – d'alar'cop Apr 08 '13 at 10:25
  • Your Temp table should start with **#**. Since OP is using **SQL Server**. – Prahalad Gaggar Apr 08 '13 at 10:27
  • @Luv - which of the tags [sql], [oracle] and [sql-developer] indicate the OP is using SQL Server? The question also states it is Oracle. Temporary tables as used in SQL Server are not common or usually appropriate in Oracle. – Alex Poole Apr 08 '13 at 10:29
  • OP: if the answer was useful enough for you to solve your problem, please tick it. If not, please say what is the current problem – d'alar'cop Apr 08 '13 at 11:59