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
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.
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.