[SOL-179] IMPORT data from Oracle over JDBC Created: 12.09.2014  Updated: 18.09.2020  Resolved: 18.09.2020

Status: Obsolete
Project: Solution Center
Component/s: EXASolution
Affects Version/s: EXASolution 5.0
Fix Version/s: None

Type: How To
Reporter: Captain EXASOL Assignee: Captain EXASOL
Labels: None

Attachments: PNG File EXAoperation_add_Oracle_JDBC.PNG     PNG File EXAoperation_upload_jar.PNG     PNG File shutdown-1.png     PNG File urandom-1.png    
Issue Links:
Related
Solution:

Which JDBC driver for Oracle shall I use?

The regular EXASolution installation already contains the Oracle thin JDBC driver preconfigured.
If you are using the community edition EXASolo you need to configure the Oracle thin JDBC driver in EXAoperation.
Click the "Add" button in Software -> JDBC drivers and configure the Main class and the prefix of the JDBC-URL:

Next upload the Oracle JDBC driver (e.g. ojdbc6.jar):

If you want to achieve best performance, consider to use the native Oracle interface (OCI) which is available through additional license options.

CREATE CONNECTION

In EXAplus execute the following query to create a connection object in EXASolution:

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

Write IMPORT/EXPORT Statement

Test the connectivity by querying the Oracle system catalog, e.g.:

select * from 
(
import from jdbc at ora_connection
statement 'select * from all_tables'
);

If the query returns a resultset, you are ready to IMPORT some tables.
Otherwise please check your network settings and/or the settings of your Oracle instance.

Now you can IMPORT/EXPORT data from Oracle, e.g.:

import into STAGE_TABLE from jdbc at ora_connection
statement 'select * from MYORATABLE';

export SUMMARY_TABLE into jdbc at ora_connection table SUMMARY_TABLE;

Troubleshooting

If you are getting an "IO Error: connection reset" when trying to import data from Oracle over JDBC
(also see https://community.oracle.com/message/3701989) do the following steps in EXAoperation:

  • Shutdown database instance
  • Edit the database instance and add the following extra parameter
    -etlJdbcJavaEnv -Djava.security.egd=/dev/./urandom
    


  • Startup the database instance
Category 1: ETL / Data Integration
Category 2: SQL

 Comments   
Comment by Thomas Bestfleisch [ 16.08.2016 ]

Adding Troubleshooting information

Generated at Sun Oct 17 08:58:46 CEST 2021 using Jira 7.13.18#713018-sha1:e1230154f8ff8cc9272975bf568fc732e806fd68.