Create Connection
This article explains how to set up the connection between
Before you begin the migration procedure, you must upload the
We recommend using Oracle Call Interface (OCI) with Oracle Instant Client instead of Oracle JDBC to connect to Exasol. This article describes both methods.
Oracle JDBC driver
Download the JDBC driver
Download the compatible driver for your version of Oracle from the Oracle JDBC downloads page .
Add the JDBC driver in Exasol
Create a configuration file called settings.cfg
with the following settings:
DRIVERNAME=Oracle
PREFIX=jdbc:oracle:thin:
FETCHSIZE=100000
INSERTSIZE=-1
The driver jar files and the configuration file must be uploaded to BucketFS in Exasol. By default, the database is configured to use the following path for all JDBC drivers:
/buckets/bfsdefault/default/drivers/jdbc/
Examples:
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.
curl -v --insecure -X PUT -T settings.cfg https://w:$WRITE_PW@$DATABASE_NODE_IP:2581/default/drivers/jdbc/exasol/settings.cfg
curl -v --insecure -X PUT -T ojdbc$JDBC_DRIVER_VERSION.jar https://w:$WRITE_PW@$DATABASE_NODE_IP:2581/default/drivers/jdbc/exasol/ojdbc$JDBC_DRIVER_VERSION.jar
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.
Create and test the connection
- 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'; - Test the connection by performing a small dummy import: Copy
IMPORT FROM JDBC AT oracle_jdbc
STATEMENT 'SELECT ''Connection works'' from dual';
If the connection fails, try disabling the security manager by adding the line NOSECURITY=YES
to the settings.cfg file.
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. -
In AWS deployments, the Amazon EC2 security group must contain an inbound rule to allow traffic on the port used for the
default
bucket in BucketFS. The default port for this bucket is 2581. For more information, refer to the AWS documentation.
Always change the default write password of the default
bucket to a secure password. For more information, see Change Bucket Password.
Install Oracle Instant Client
-
Download the compatible version of Oracle Instant Client to your local machine.
For Exasol 8.31.0 and earlier, Oracle Instant Client 12.1.0.2.0 is the only supported version of the client. You can download this version from the Oracle Instant Client Downloads page.
For Exasol 8.32.0 and later, Oracle Instant Client 23.5.0.24.07 is required. This version of the client has been deprecated by Oracle and is therefore not available on the Oracle website. You can download this client using this direct link.
-
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.
curl -v --insecure -X PUT -T instantclient-basic-linux.x64-23.5.0.24.07.zip https://w:$WRITE_PW@$DATABASE_NODE_IP:2581/default/drivers/oracle/instantclient-basic-linux.x64-23.5.0.24.07.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:
CREATE OR REPLACE CONNECTION OCI_ORACLE
TO '192.168.99.103:1521/xe'
USER 'system'
IDENTIFIED BY 'oracle';
SELECT * FROM (
IMPORT FROM ORA at OCI_ORACLE
STATEMENT 'select ''Connection works'' from dual'
);