0

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.

kawsleo
  • 560
  • 4
  • 23
  • Have you seen this post? https://stackoverflow.com/questions/43746258/query-a-database-based-on-result-of-query-from-another-database – Max Zolotenko Dec 17 '19 at 18:50
  • 2
    Oracle has a hard limit of 1000 constant values for the IN operator. –  Dec 17 '19 at 18:51
  • 1
    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

1 Answers1

0

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>
);
Dr Y Wit
  • 2,000
  • 9
  • 16