Sparx Systems Forum

Enterprise Architect => General Board => Topic started by: Richard Freggi on August 02, 2023, 11:30:49 pm

Title: Help I forgot how to set up Oracle DB reverse engineering in my Windows 10
Post by: Richard Freggi on August 02, 2023, 11:30:49 pm

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.

**PHEW**

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.]

Title: Re: Help I forgot how to set up Oracle DB reverse engineering in my Windows 10
Post by: Paolo F Cantoni on August 03, 2023, 11:07:57 am

Thanks, Richard!
Good to know!

Perhaps the documentation can be upgraded?

Paolo

Title: Re: Help I forgot how to set up Oracle DB reverse engineering in my Windows 10
Post by: Richard Freggi on August 03, 2023, 04:43:03 pm

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.

Title: Re: Help I forgot how to set up Oracle DB reverse engineering in my Windows 10
Post by: Modesto Vega on August 03, 2023, 07:30:20 pm

Richard,

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