Configuring Xtraction to connect to an Oracle Database

Version 1

    Xtraction has the ability to connect to Oracle databases. To allow this, the Oracle client needs to be installed on the Xtraction server. The instructions below detail how to configure the Xtraction server to enable connections to an Oracle database.

     

    1. Download the Oracle Instant Client to the Xtraction Server

     

    From the Oracle website, download the latest release of the Oracle Instant Client. At the time of writing, the latest version of the Instant Client is 12.1 and is available here. You will need to either log in with an existing Oracle.com account or create a new Oracle.com account to download the client. 

     

    2. Unzip the Oracle Instant Client to a location on the Xtraction Server

     

    The Oracle Instant Client does not require an installation package be executed. To install the Instant Client, open the zip file downloaded in the previous step and copy the instant_client_12_1 to a folder on the Xtraction Server, for example C:\Oracle\, as shown below. If you have downloaded a newer version of the Instant Client, the version name in the folder will be different.

     

     

     

    3. Obtain or create a file named tnsnames.ora

     

    The Oracle Instant Client uses a file name tnsnames.ora that defines connection details for Oracle database servers within an environment.  This file can usually be supplied by the team that supports the Oracle database server Xtraction will be connected to. Below is an example of the content of the tnsnames.ora file - if this file cannot be obtained, you can create this file manually using the example below, making sure to specify the correct host, port and service name (SERVICE_NAME). The Server name (ORACLE_SERVER in the example below) should be updated to reflect your environment. If the Xtraction server will be connecting to more than one Oracle server, connection details for each server will need to be added to tnsnames.ora

     

    ORACLE_SERVER = (DESCRIPTION =

        (ADDRESS_LIST =

          (ADDRESS = (PROTOCOL = TCP)(HOST = 10.59.34.231)(PORT = 1521))

        )

        (CONNECT_DATA =

          (SERVICE_NAME = orcl)

        )

      )

     

    4. Add tnsnames.ora file to the Instant Client TNS_ADMIN folder

     

    In Windows Explorer, open the folder C:\Oracle\instantclient_12_1 and create a new folder named TNS_ADMIN. In this folder, paste the file tnsnames.ora from the previous step.

     

    5. Add/update the System Environment Variables

     

    From the Control Panel, select System and Security > System, then select Change Settings (administrator privileges required). On the System Properties screen, go to the Advanced tab, and select Environment Variables. First, find the Path variable in the System Variables section. Double click on Path, and add a semi colon followed by the path to the instant client to the end of this variable and click OK:

    Next, in the System Variables section, select New to create a new Environment Variable. Name this variable TNS_ADMIN, and specify the value as the path to the TNS_ADMIN folder created in step four, then click OK:

    Click OK, then OK again to return to the Control Panel.

     

    6. Restart the Xtraction Server

     

    Following the changes to the System Environment Variables, restart the Xtraction server to ensure if picks up the new values.

     

    7. Test connection to a Oracle database in the Data Model Editor

     

    Open the Data Model Editor and add a new Oracle data source to a new or existing data model. Enter the details of your Oracle database server in the Connection Details form (example below), using the server name specified in the tnsnames.ora file.

    Click the Test button to confirm the connection to the Oracle database is working successfully.