How can I connect to an Oracle database from my Streaming and/or LiveView applications?

First, determine the correct URL of your Oracle database. The URL should be in one of the following formats:

• jdbc:oracle:thin:@<dbhost>:<dbport>:<sid>
• jdbc:oracle:thin:@<dbhost>:<dbport>/<sn>

..where:

• dbhost = the database hostname
• dbport = the database listen port (default is 1521)
• sid = the service id for the database 
• sn = the service name alias for the database

To see what service name aliases are available for connection requests, login as the SYS account and execute the query..

select con_id, name from v$services;

To quickly spin up an Oracle db for simple testing purposes, you can use Docker..

docker pull store/oracle/database-enterprise:12.2.0.1
docker run -d -it --name oracle12 -p 1521:1521 -p 5500:5500 store/oracle/database-enterprise:12.2.0.1

This will start a local Oracle 12.2.0.1 database with SID ORCLCDB and service name alias orclpdb1.localdomain. For this database, you would use one of the following connection URLs:

• jdbc:oracle:thin:@localhost:1521:ORCLCDB
• jdbc:oracle:thin:@localhost:1521/orclpdb1.localdomain

Next, install a compatible ojdbc driver into your Maven repository. To determine the correct driver for your Oracle DB version, refer to Oracle's guidance here. Typically, it is best to download the latest driver that is compatible with both your Oracle database version and the JDK version used to run your Streaming application. For example:

mvn install:install-file -Dfile=ojdbc8-19.3.0.0.jar -DgroupId=com.oracle.ojdbc -DartifactId=ojdbc8 -Dversion=19.3.0.0 -Dpackaging=jar

Then add the driver as a dependency in your project's pom.xml:

<dependency>
    <groupId>com.oracle.ojdbc</groupId>
    <artifactId>ojdbc8</artifactId>
    <version>19.3.0.0</version>
</dependency>

Finally, create a JDBC HOCON configuration:

configuration = {
  JDBCDataSourceGroup = {
    jdbcDataSources = {
      "oracle12" = {
          driverClassName = "oracle.jdbc.driver.OracleDriver"
          serverURL = "jdbc:oracle:thin:@localhost:1521/orclpdb1.localdomain"
          userName = "SYS AS SYSDBA"
          password = "Oradoc_db1"
      }
    }
  }
}

You are now configured to connect to your Oracle JDBC data source.
 

Outlines the steps to connect to an Oracle JDBC data source.

For additional details on JDBC HOCON configuration, refer to the Help under Home > Configuration Guide > StreamBase Configuration Types > StreamBase JDBC DataSource Configuration