Uploaded image for project: 'Solution Center'
  1. Solution Center
  2. SOL-179

IMPORT data from Oracle over JDBC

    XMLWordPrintable

    Details

    • Type: How To
    • Status: Published
    • Affects Version/s: EXASolution 5.0
    • Fix Version/s: None
    • Component/s: EXASolution
    • Labels:
      None
    • Solution:
      Hide

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

      Attachments

        Activity

          People

          • Assignee:
            CaptainEXA Captain EXASOL
            Reporter:
            CaptainEXA Captain EXASOL
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated: