Passing 60000 Values from SSIS to ORACLE where condition to PULL data from ORACLE

Question

I am trying to pull data from oracle database into SQL Server using SSiS packages. I have a list of 60000 customer ids for which I need to pull data from Oracle.

When I am creating a SSIS package where I am manually passing 500 ids then Oracle query runs fine but if I try with even 10000 customer IDs, It does not work.

Please suggest if there is a better way to achieve this.

Note:- I don't have write access on Oracle. Creating tables/views in oracle is not possible for me.

Have you seen this post? https://stackoverflow.com/questions/43746258/query-a-database-based-on-result-of-query-from-another-database — Максим Золотенко, Dec 17 '19 at 18:50
Oracle has a hard limit of 1000 constant values for the IN operator. — a_horse_with_no_name, Dec 17 '19 at 18:51
Data Flow Task - source query is the 60k values. Lookup task - no cache against Oracle source (and then giggle as 60k queries fire off against your oracle instance) — billinkc, Dec 17 '19 at 20:57

score 0 · Answer 1 · answered Dec 17 '19 at 19:08

Do not you think it makes sense to provide exact error when instead of saying "it does not work"?

If it fails with ORA-01795: maximum number of expressions in a list is 1000 then you can work around it by providing list of tuples instead of list of atomic values.

Replace <list_atomic> with the value of list_atomic and <list_tuples> with value of list_tuples to see how it works.

select regexp_replace(xmlcast(xmlagg(xmlelement("x", rownum, ',') order by rownum)
                              .extract('//x/text()') as clob), ',$') list_atomic
  from dual
connect by level <= 1001;

select * from v$session
 where (sid) in
(
<list_atomic>
);

select regexp_replace(xmlcast(xmlagg(xmlelement("x", '(0,'||rownum||')', ',') order by rownum)
                              .extract('//x/text()') as clob), ',$') list_tuples
  from dual
connect by level <= 1001;

select * from v$session
 where (0, sid) in
(
<list_tuples>
);

Passing 60000 Values from SSIS to ORACLE where condition to PULL data from ORACLE

1 Answers1