0

I've got this issue with a query in SSIS. From a table in SQL Server I'm getting over 25000 different identifiers. These identifier are associated to many values in a table in one Oracle Database. This is the schema that I have implemented for doing this.

enter image description here

The problem is that some days the identifiers can be over 45000, and at this point perform a loop for every one is not the best solution (It can take to much time to get the result). Previously I have performed another query where from the SQL statement, I am creating and sending a unique row with all the values concatenated and then I have recover this unique string from an object and use it to create the query in the ODBC Source that invoke the table in Oracle: something like this:

'Select * from Oracle_table' + @string_values

with @string_values = 'where value in (........)'. It works good because the number of values is small enough to be used, like 250. But in this case I can not use this approach because the number is really big and obviously the DBA of Oracle is going to cancel the query.

So I wonder, how can I iterate over the object getting only a few number of values everytime, something like 300 or maximun 500, to avoid the cancelation of the query but at the same time doing the minimun number of loops. Or if there another solution to this question I would appreciate your help.

d2907
  • 798
  • 3
  • 15
  • 45
  • If Oracle has some kind of table-valued parameter, you can use that to pass in the identifiers, and then just join to it in Oracle. – Tab Alleman Aug 11 '15 at 13:31
  • That is the problem. In Oracle I can only READ the tables nothing else. – d2907 Aug 11 '15 at 14:05
  • Then maybe your best bet is to use a dataflow to move the identifiers into a new table in Oracle, and then join to that table in an Oracle proc. – Tab Alleman Aug 11 '15 at 14:18
  • Man, I am telling you. In oracle I can do nothing. That Database is only used as a source and is control for another area of the company, I can ONLY read the tables present in ORACLE nothing else. That is why I am asking this. – d2907 Aug 11 '15 at 14:51

1 Answers1

2

Ok, what you can do is a hybrid of looping through every individual identifier and building a string to use with a "WHERE value IN (...)" clause.

Create a loop where you get as many identifiers as you can without causing a "WHERE value IN (...)" to get too big. So say you get the top 100 or 200 or however many it is.

EDIT: One way to do the above is use a variable for Loop Control. If you want 100 at a time, start the variable at 100. Use it to get the Identifiers with ROW_NUMBER() between 1 - 100 (the logic in your query would be something like: BETWEEN (MyVariable - 99) AND MyVariable). Then at the end of the loop, increment your variable's value by 100.

Your only loop is against the variable using this approach. Your query builds a string of identifiers. Here is an example of this:

Concatenate many rows into a single text string?

You use that string to build your Oracle query that uses WHERE value IN (The string of identifiers)

Run that "WHERE IN" query against Oracle, store that data either on SQL or in memory, and then loop and get the next batch of identifiers. Repeat the loop until you've gone through all the identifiers you need.

Community
  • 1
  • 1
Tab Alleman
  • 31,483
  • 7
  • 36
  • 52
  • Thanks @Tab Alleman for your reply. But this is precisely what I am asking. How can I control this situation in a loop. I mean, get a subset of id's and send it as a string to the odbc source (to oracle)?. A loop inside another loop?. Or is there another possibility for this? (Maybe I was not clear enough, English is not my mother tongue) – d2907 Aug 11 '15 at 15:21
  • Edited my answer with more detail. – Tab Alleman Aug 11 '15 at 15:40
  • thank a lot (once again).I'm gonna try to implement this solution. I am using this answer for the creation of the string, I think it is very simple and easy to implement http://stackoverflow.com/questions/30446363/problems-with-the-execution-of-a-sp-in-sql – d2907 Aug 11 '15 at 16:22