Author Topic: Help I forgot how to set up Oracle DB reverse engineering in my Windows 10  (Read 1599 times)

Richard Freggi

  • EA User
  • **
  • Posts: 477
  • Karma: +18/-7
    • View Profile
I've been reverse engineering PowerBI and PostgreSQL databases a lot in past few years, but it's been a long time since I worked on an Oracle one (this one is 19C 64 bit).  I remember it worked well when I did it.
The oracle help pages and various forums tell you how to install a client to query but not how to connect with EA.  Could anyone help me remember?  Are these steps correct:
1. Download instant client basic lite and instant client ODBC for windows 10 64 bit
2. Unzip instant client and ODBC to C:\ORACLE_HOME directory; the unzip adds C:\ORACLE_HOME\network and C:\ORACLE_HOME\network\admin. 
3. Unzip the odbc driver so that the odbc_install.exe file is in C:\ORACLE_HOME\ directory
4. Get the tsnames.ora file and paste it in C:\ORACLE_HOME\network\admin
5. Right click on odbc_install.exe and run as administrator
6. Open Windows 10 ODBC Data Sources (64-bit) and in System DSN tab, fill in the driver configuration.   However I'm not  getting the tsnames.ora data filled in in the ODBC driver configuration tab like I should
7. Open Windows environment variables and add a user variable Path with value C:\ORACLE_HOME.  Save.
8. Open EA 16, Settings / ODBC Data Sources, make sure the new data source is listed
9.  In EA, Develop / Import and reverse engineer.


I'm curious if the whole oracle client and tsnames.ora are needed or can I just download the driver file and run odbc_install.exe?  Then add the TSA file parameters directly in the Windows ODBC manager window.

I don't yet have a login to try so would appreciate if anyone can share experience thanks!

[EDIT: found the problem!  I needed to include system variable TNS_ADMIN with value C:\ORACLE_HOME\network\admin in my environment variables.  Works now.]

« Last Edit: August 03, 2023, 05:14:52 pm by Richard Freggi »

Paolo F Cantoni

  • EA Guru
  • *****
  • Posts: 8554
  • Karma: +254/-129
  • Inconsistently correct systems DON'T EXIST!
    • View Profile
Thanks, Richard!
Good to know!

Perhaps the documentation can be upgraded?

Inconsistently correct systems DON'T EXIST!
... Therefore, aim for consistency; in the expectation of achieving correctness....
Helsinki Principle Rules!

Richard Freggi

  • EA User
  • **
  • Posts: 477
  • Karma: +18/-7
    • View Profile
More info as PSA
- The EA 'native' driver worked the same as the oracle ODBC driver
- I could not import sequences with either driver; I had to to exclude them from import
- I did not try to import triggers and packages because had no time to play around too much with import settings
- With both drivers import was super slow, 20 minutes for ~100 tables and their columns.  Columns are what slows down the import, eg import of views was very fast
- p.s. connecting with DBeaver and creating ERDs with DBeaver was effortless.  I use both DBeaver and Sparx for different tasks.
« Last Edit: August 03, 2023, 05:15:11 pm by Richard Freggi »

Modesto Vega

  • EA Practitioner
  • ***
  • Posts: 1055
  • Karma: +28/-8
    • View Profile

From memory (and I have not done this for a very long time), you need to install the Oracle Data Access Components (ODAC) and use the Oracle Provider for OLE DB. If you are using v16 64-bit, you are likely to need the ODAC 64-bit; but if you are running v16 32-bit or v15 it may be more complicated