Before proceeding you will need to know the following information to configure the ODBC data source, contact your IT administrator for assistance.
Prerequisites:
- network address of the database server (ex. DNS or TCP/IP address)
- database user account and password
- database port if non-default was set
- SID (Oracle System ID) of the service for the database (default: rds)
Before continuing, make sure you have installed the Oracle InstantClient ODBC driver on your computer. You will need to download both the basic instant client files and the additional ODBC files.
- Instant Client Package – Basic (ex. instantclient-basic-nt-12.1.0.1.0.zip)
- Instant Client Package – ODBC (ex. instantclient-odbc-nt-12.1.0.1.0.zip)
Installation
- Extract both of the archives in to the same directory (ex. C:\drivers\Oracle\instantclient_12_1). You may be prompted to merge the folders, click on Yes to continue.
- Add the instantclient directory to your PATH environment variable. Go to Control Panel > System > Advanced system settings. Click on Environment Variables on the bottom of the Advanced tab. Select Path from the list of System variables and click on Edit. Add a semicolon at the end and enter the directory the full path of the instant client files (ex. %SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem;%SYSTEMROOT%\System32\WindowsPowerShell\v1.0\;C:\drivers\Oracle\instantclient_12_1). Click on Ok to save the changes.
- Add another system variable by clicking on New called TNS_ADMIN and set its value to point to the directory containing the instant client files. (ex. C:\drivers\Oracle\instantclient_12_1). This system variable tells the Oracle instant client driver where the tnsnames.ora file is located. We will discuss on how to configure the file later. Click on OK to create it and click OK on the remaining dialogs to finish.
- Open a command prompt window as an Administrator. To run a program as an Administrator, right-click on the program’s icon and select “Run as administrator”.
- Change the directory to the instant client directory (ex “cd C:\drivers\oracle\instantclient_12_1”).
- Run odbc_install.exe to install the ODBC driver package. The installation program will run and display a success message once completed.
- Due to the architecture of the ODBC driver, it requires a tnsnames.ora file that contains the service names of the oracle databases. Open a editor such as notepad and copy the following:
<addressname> =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = <hostname>)(PORT = <port>))
(CONNECT_DATA =
(SID = <sid>)
)
)
- Your TEAMS-RDS administrator will provide you with the hostname, port and sid of the database server hosting the TEAMS-RDS database. Replace the parameters above with those provided by your administrator. Below is an example of the tnsnames.ora with connection parameters set to a database server. The addressname is user defined and you may set it as “RDS”.
RDS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracle)(PORT = 1521))
(CONNECT_DATA =
(SID = RDS12M)
)
)
- Save the file as “tnsnames.ora” in the directory with the instant client files (ex. C:\drivers\oracle\instantclient_12_1). The TNS_ADMIN system environment variable previously configured tells the driver where to find the “tnsnames.ora” file. When saving the file make sure its extension is “.ora”, set the “Save as type” in notepad as “All File (*.*)”.
- Now let’s test the Oracle installation, for 32-bit Windows open Control Panel -> Administrative Tools -> Data Sources (ODBC). For 64-bit Windows, open <Windows Dir>\SysWOW64\odbcad32.exe. This launches the ODBC Data Source Administrator, it manages the data sources on your machine. Go to System DSN and click on Add. Select “Oracle in instantclient_XX_X” from the list and click on Finish. If it works skip the following steps concerning the Microsoft Visual C++ 2010 Redistributable Package.
- If you get an error 126, most likely you do not have Microsoft Visual C++ 2010 Redistributable Package installed on your computer. Close the ODBC Data Source Administrator utility. Use your favorite search engine to locate the Microsoft web page to download the redistributable package. Once downloaded, run the installer and follow the prompts.
- Upon completion of the installation, open the ODBC Data Source Administrator utility.
- Enter a data source name, this is user defined and should be describe the data source. For example you may enter “RDS_ORACLE” to remind you that the data source connects to a TEAMS-RDS database on a Oracle database server. Select the drop-down menu and pick the service name previously created. Enter the database user id provided by your administrator. Click on Test Connection, enter the password provided by your administrator and click on OK to test the connection parameters.
- Once you have successfully tested the ODBC driver configuration, click on OK to save it. You will now be able to configure TEAMS-Designer to import and export models to an Oracle database.
