Installing and configuring Oracle OCI to be used as the data mart for reports

This article applies to version 5.6 onwards of all platform Talend Studio licenses with Data Quality.

If you want to define the report data mart using Oracle OCI, you must first install the OCI client and then configure the Talend Studio .ini file as described below.

This example supposes that you use Oracle 11gr2 and you want to configure OCI on Linux x86.

1. Download the appropriate instant client zip file for Linux x86.

   Check Instant Client Downloads for Linux x86 ( Instant Client Downloads for Linux x86-64 ) for download links and detail information about installation.

   The most important files to have are the .so and .h head files: oracle-instantclient11.2-basic-11.2.0.2.0.i386.rpm and oracle-instantclient11.2-devel-11.2.0.2.0.i386.rpm .

2. Install and configure Oracle OCI client, use alien , dpkg and aio :

   1. Install alien .

      sudo apt-get install alien

   2. Transform rpm to deb .

      sudo alien ./oracle-instantclient11.2-basic-11.2.0.2.0.i386.rpm
      sudo alien ./oracle-instantclient11.2-devel-11.2.0.2.0.i386.rpm

   3. Install the deb files.

      sudo dpkg -i ./oracle-instantclient11.2-basic_11.2.0.2.0-2_i386.deb
      sudo dpkg -i ./oracle-instantclient11.2-devel_11.2.0.2.0-2_i386.deb
      

   4. Install aio.

      sudo apt-get install libaio-dev
      

      After installation, the two .deb files will be located at /usr/lib/oracle/11.2/client/lib and /user/include/oracle/11.2/client respectively.

3. Set the path of the LD_LIBRARY_PATH environment variable. You can use, for example:

   vim ~/.bashrc
   export LD_LIBRARY_PATH=/opt/oracle/instantclient_12_1:$LD_LIBRARY_PATH 

   Alternatively, you can add this path by using ldconfig but make sure first that there is no other Oracle software that will be impacted.

This example supposes that you use Oracle 10gr2 and you want to configure OCI on Windows x86.

1. Download the appropriate instant client zip file for Windows x86. Installation requires the basic package.

   Check Instant Client Downloads for Windows x86 ( Instant Client Downloads for Windows x86_64 ) for download links and detail information about installation.

2. Install and configure Oracle OCI client.

   1. Unzip all the files to a folder, for example c:\oracle_oci_client .

   2. Add the following system environment variables:
      • CLASSPATH

        c:/oracle_oci_client\ojdbc14.jar

      • LD_LIBRARY_PATH

        c:\oracle_oci_client

3. On Windows:

   1. Change the Oracle driver from thin to oci , you must change the url as the following:

      thin: jdbc:oracle:thin:@ <host>:<port>:<db name>

      oci: jdbc:oracle:oci8:@ <tns name>

   2. Copy tnsnames.ora to c:\oracle_oci_client , and add tns configuration as the following:

      OCI39 =
      
        (DESCRIPTION =
      
          (ADDRESS_LIST =
      
            (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.31.39)(PORT = 1521))
      
          )
      
          (CONNECT_DATA =
      
            (SID = root)
      
          )
      
        )

1. Install and configure the Oracle OCI client appropriate to your system as described above.

2. Add the below parameter into the Talend Studio .ini file.

   -Djava.library.path=c:\oracle_oci_clien

3. Start your Talend Studio.

4. From the Profiling perspective, set Oracle OCI as the report data mart from the report editor or from the Preferences window.