Technical Exasol

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

09 Apr 2019 | Share

Virtual Schema

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.

exasol-oracle

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

community edition exasol

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

Oracle-Exasol

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

Exasol-oracle-3

 

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:\oracle\instantclient_12_2\network\admin\tnsnames.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)\EXASOL\EXASolution-6.0\EXAplus
λ 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

Considering implementing an in-memory database?

Free White Paper

Looking For An In-Memory Analytic Database

White Paper: Business Technology Predictions 2019 

Free White Paper

white paper download

Pre-register to save your copy of the upcoming Business Technology Predictions 2019 White Paper.