How to EXPORT/IMPORT between Exasol’s database and Oracle’s

The commands IMPORT and EXPORT provide an easy way to transfer data between Exasol’s in-memory analytical database, and other data sources like an Oracle database. But if you try to do this, you might see this error message: Oracle instant client not available. Please ask your administrator to install it via EXAoperation.

Here’s how to do that yourself without having to ask your admin.

I’m using the free Exasol Community Edition. The highlighted part shows how to access ExaOperation. Next, click ‘Software’:

Now go to Oracle Downloads and pick the right Instant Client as shown on the picture.

Back in ExaOperation, you upload that ZIP-file from where you downloaded it on your local machine, then hit Submit:

And that’s it. To check everything’s set up use ‘Show Installation History’.

Next, you need to prepare your Oracle sandbox:

λ sqlplus sys/oracle@orcl as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Fri Jun 29 13:39:04 2018

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> create user ora_user identified by ora_password;

User created.

SQL> grant dba to ora_user;

Grant succeeded.

SQL> create table ora_user.ora_table (n number, m varchar2(20));

Table created.

SQL> insert into ora_user.ora_table values (1,'JUST SOME TEXT');

1 row created.

SQL> commit;

Commit complete.

The connect descriptor orcl above is resolved by a textfile named tnsnames.ora:

λ type c:oracleinstantclient_12_2networkadmintnsnames.ora
ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.102)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

Now I continue to prepare my Exasol sandbox:

C:Program Files (x86)EXASOLEXASolution-6.0EXAplus
λ exaplusx64 -c 192.168.56.101:8563 -u sys -p exasol -lang EN
EXAplus 6.0.8 (c) EXASOL AG

Friday, June 29, 2018 3:09:10 PM CEST
Connected to database EXAone as user sys.
EXASolution 6.0.8 (c) EXASOL AG

SQL_EXA> create schema exa_schema;
EXA: create schema exa_schema;

Rows affected: 0

SQL_EXA> create table exa_schema.exa_table (col1 double, col2 varchar(30));
EXA: create table exa_schema.exa_table (col1 double, col2 varchar(30));

Rows affected: 0

SQL_EXA> insert into exa_schema.exa_table values (2,'ANOTHER TEXT');
EXA: insert into exa_schema.exa_table values (2,'ANOTHER TEXT');

Rows affected: 1

Now there’s one table in Exasol and a similar table in Oracle. Next step is to create a connection from Exasol to Oracle:

create or replace connection ora_conn to
'(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.102)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )'
user 'ora_user' identified by 'ora_password';

Now we are ready to transfer data between the two databases. Oracle to Exasol:

SQL_EXA> import into exa_schema.exa_table from ora at ora_conn table ora_user.ora_table;
EXA: import into exa_schema.exa_table from ora at ora_conn table ora_user.o...

Rows affected: 1

SQL_EXA> select * from exa_schema.exa_table;
EXA: select * from exa_schema.exa_table;

COL1              COL2
----------------- ------------------------------
                2 ANOTHER TEXT
                1 JUST SOME TEXT

2 rows in resultset.

The other way: Exasol to Oracle

SQL_EXA> export exa_schema.exa_table into ora at ora_conn table ora_user.ora_table;
EXA: export exa_schema.exa_table into ora at ora_conn table ora_user.ora_ta...

Rows affected: 2

Let’s check how that looks at the Oracle side now:

λ sqlplus sys/oracle@orcl as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Fri Jun 29 15:24:52 2018

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select * from ora_user.ora_table;

         N M
---------- --------------------
         1 JUST SOME TEXT
         2 ANOTHER TEXT
         1 JUST SOME TEXT

Easy, isn’t it? Some more examples:

SQL_EXA> import into exa_schema.exa_table (col2) from ora at ora_conn 
                 statement 'select m from ora_user.ora_table where n=2';
EXA: import into exa_schema.exa_table (col2) from ora at ora_conn statement...

Rows affected: 1

SQL_EXA> select * from exa_schema.exa_table;
EXA: select * from exa_schema.exa_table;

COL1              COL2
----------------- ------------------------------
                2 ANOTHER TEXT
                1 JUST SOME TEXT
                  ANOTHER TEXT

3 rows in resultset.
SQL_EXA> export (select col1 from exa_schema.exa_table where col2 like 'JUST%') into ora at ora_conn
            statement 'insert into ora_user.ora_table (n) values (:1)';
EXA: export (select col1 from exa_schema.exa_table where col2 like 'JUST%')...

Rows affected: 1
SQL> select * from ora_user.ora_table;

         N M
---------- --------------------
         1 JUST SOME TEXT
         1
         2 ANOTHER TEXT

Check the fine manual for some more details: https://docs.exasol.com/sql/import.htm