Create Connection

Learn how to set up the connection between Oracle and Exasol.

Before you begin the migration procedure, you must upload the Oracle JDBC driver or Oracle Instant Client to your Exasol system and configure a connection between the databases. 

We recommend using Oracle Call Interface (OCI) with Oracle Instant Client instead of Oracle JDBC to connect to Exasol. This article describes both methods.

To learn how to install drivers in Exasol SaaS, see Driver management (SaaS).

Oracle JDBC driver

  1. Download the JDBC driver from the Oracle JDBC Driver download site. Make sure that you download the correct version of the JDBC driver for the version of the Oracle database.

  2. Upload the JDBC driver to EXAoperation, Exasol's management interface.

    1. Log in to the EXAoperation user interface as an administrator user.
    2. Select Configuration > Software and click the JDBC Drivers tab. Click Add to add the JDBC driver details.
    3. Enter the following details for the JDBC properties:

      • Driver Name: Oracle

      • Main Class: oracle.jdbc.driver.OracleDriver

      • Prefix: jdbc:oracle:thin:

      • Disable Security Manager: Do not select this option. This option allows the JDBC Driver to access the certificate and additional information.

      • Comment: This is an optional field.

    4. Click on Add to save the settings.
    5. Select the radio button next to the driver from the list of JDBC drivers.
    6. Click on Choose File to locate the downloaded driver, then click on Upload to upload it.

Create and test the connection

  1. Open a SQL client and create the connection from Exasol to Oracle:
    Copy
    CREATE OR REPLACE CONNECTION oracle_jdbc    
    TO 'jdbc:oracle:thin:@192.168.56.106:1521/orcl' 
    USER 'stage' IDENTIFIED BY 'stage';
  2. Test the connection by performing a small dummy import:
    Copy
    IMPORT FROM JDBC AT oracle_jdbc 
    STATEMENT 'SELECT ''Connection works'' from dual';

Oracle Instant Client

Prerequisites

Traffic must be allowed on the port used for the default bucket in BucketFS. The default port for this bucket is 2581.

Always change the default write password of the default bucket to a secure password. For more information, see Change bucket passwordChange bucket password.

Install Oracle Instant Client

  1. Download the compatible version of Oracle Instant Client to your local machine.

  2. Upload the zip archive containing the client to BucketFS in Exasol. By default, the database is configured to use the following path for Oracle Instant Client:

    /buckets/bfsdefault/default/drivers/oracle/

Example:

The following examples use curl on a Linux terminal, but you can also use other interfaces and tools.

Placeholder values in the examples are styled as Bash variables, for example: $NODE_IP. Replace these placeholders with your own values.

Copy 
curl -v --insecure -X PUT -T instantclient-basic-linux.x64-12.1.0.2.0.zip https://w:$WRITE_PW@$DATABASE_NODE_IP:2581/default/drivers/oracle/instantclient-basic-linux.x64-12.1.0.2.0.zip

The option --insecure or -k tells curl to bypass the TLS certificate check. This option allows you to connect to a HTTPS server that does not have a valid certificate. Only use this option if certificate verification is not possible and you trust the server.

Verification

To verify that Oracle Instant Client is installed, perform an IMPORT statement using the IMPORT FROM ORA syntax. For example:

Copy 
CREATE OR REPLACE CONNECTION OCI_ORACLE
    TO '192.168.99.103:1521/xe'
    USER 'system'
    IDENTIFIED BY 'oracle';
Copy 
SELECT * FROM (
    IMPORT FROM ORA at OCI_ORACLE
    STATEMENT 'select ''Connection works'' from dual'
);