0

Environment: Small corporate with hundreds of employees using Excel and consuming DWH data.

Scenario: refreshing data from Oracle DWH to 32bit Excel via Oracle OLEDB provider on daily basis

Problem: with large data sets (hundreds of thousands of rows) Excel eats up over 3GB RAM and crashes

Tested: the same data with different Excel versions (2013, 2016, 32bit, 64bit), different OLEDB provider (MSDAORA, Oracle 12.1 - 19)

Findings:

  • MSDAORA is much less memory intensive, but very slow and formally unsupported for years. And what is worse - there are rumours, that it doesn't work with Oracle 19 DB and we are upgrading to 19 in some months.
  • OraOLEDB 32-bit - until version 12.1 it worked fine, since 12.2 the memory consumption is multiple times bigger, thus 32bit Excel usually crashes.
  • OraOLEDB 64-bit - I tested v19 and it worked just fine. But it is unlikely, that all of our users will get 64bit Excel because of other compatibility issues.
  • Tested CacheType=File parameter in connection string and it didn't do anything...

Questions:

  • Did you encounter the same problem?
  • Do you have a solution for this?
  • What is your approach with pulling data from Oracle DB to Excel?
Vraana
  • 1
  • 1

1 Answers1

0

MSDAORA does not work anymore with Oracle 19. However, it means Oracle Client version 19. If the Database is 19 and the client is 12.2 or lower it should still work (but of course not recommended)

What about ODBC drivers? Of course, the ODBC drivers from Oracle.

Or have a look at How to connect to Oracle 11 database from . net and check one of the third-party provided drivers.

Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110
  • ODBS and .NET - a way to go, but we have tens or maybe hundreds of existing reports built on OLEDB connections. It's easy to switch from MSDAORA to OraOLEDB, but quite time consuming to switch from OLEDB to ODBC connection. – Vraana Jun 08 '20 at 15:12