Connect to a remote Oracle database

Overview

Oracle REST Data Services (ORDS) provides a REST framework that allows access to a remote database. Some offerings within the Oracle SaaS cloud enable read-only access to databases hosted in the SaaS environment.

A database exposed through an ORDS service can be accessed from DIH using Oracle’s SQL Developer Command Line (SQLcl) along with the included JDBC driver. With this setup integrated into DIH, you can execute a SQL statement like the following to copy data from a remote database to a local database:

INSERT INTO CI_FT SELECT @ORDS FROM CI_FT...

In this statement, the @ORDS portion refers to a database context.

Prerequisites

To ensure successful ORDS integration, the following requirements must be met:

  • The DIH rtserver must operate with JDK 11 or higher. The DIH web server also runs on JDK 11, eliminating the need to maintain different versions of Java.
  • The Oracle SQLcl.zip file must be downloaded from Oracle, and the following JAR files from its lib folder need to be copied to the ria-dmi/lib directory.
    • dbtools-common.jar
    • dbtools-data.jar
    • dbtools-datapump.jar
    • dbtools-http.jar
    • dbtools-net.jar
    • dbtools-sqlcl.jar
    • jdbcrest.jar

Define parameters

Once the prerequisites are met, a database context to access the ORDS database can be defined with parameters like this:

  • Driver Class Name: oracle.dbtools.jdbc.Driver
  • URL: http://ords-db-server:8080/ords/<dbname>/
  • Username: <username>
  • Password: <password>