Loading Data from Oracle

This section describes how you can connect Oracle database with Exasol and load data.

Exasol recommends using Oracle OCI (Oracle Call Interface) instead of Oracle JDBC.

To view details about data type mappings or to migrate data, see the Oracle to Exasol migration script in our GitHub repository.

Oracle OCI

Download and add Driver

Follow the procedure described in Add Oracle Instant Client to add the thin client to your Exasol environment.

Run Statements

Run the following statements from an SQL client to check the connection.

The Oracle system must be connected to the Exasol database and the user should exist in the system before running the SQL statements.

CREATE CONNECTION

Run the following statement to create a connection.

CREATE OR REPLACE CONNECTION OCI_ORACLE
    TO '192.168.99.103:1521/xe'
    USER 'system'
    IDENTIFIED BY 'oracle';

Alternatively, you can run the following statement.

CREATE CONNECTION OCI_ORACLE TO '(DESCRIPTION =  
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.99.103)(PORT = 1521)) 
      (CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = orcl)))';

Test Connection

select * from 
(
import from ORA at OCI_ORACLE
statement 'select ''Connection works'' from dual'
);

Oracle JDBC

Download Driver

Download the compatible driver from the Oracle JDBC Driver Download Page link.

Add JDBC Driver

To add a JDBC driver, you must create a configuration file called settings.cfg. Use the below settings to create the file, replacing the JAR entry with the name of the jar file you downloaded:

DRIVERNAME=Oracle
JAR=ojdbc11.jar
DRIVERMAIN=oracle.jdbc.OracleDriver
PREFIX=jdbc:oracle:thin:
NOSECURITY=NO
FETCHSIZE=100000
INSERTSIZE=-1

Follow the procedure described in Add JDBC Drivers to upload the settings.cfg configuration file and the JDBC driver jar file(s).

Run Statements

Run the following statements from an SQL client to check the connection.

The Oracle system must be connected to the Exasol database and the user should exist in the system before running the SQL statements.

CREATE CONNECTION

CREATE CONNECTION JDBC_ORACLE
TO 'jdbc:oracle:thin:@//10.78.0.178:1521/orcl'
user 'exatest'
identified by 'test';

Test Connection

Test the connectivity by querying the Oracle system catalog.

select * from 
(
import from jdbc at JDBC_ORACLE
statement 'select ''Connection works'' from dual'
);

Load Data

You can use the IMPORT statement to load data using the connection you created above. IMPORT supports loading data from a table or a SQL statement.