Oracle database connection

From Fusion Registry Wiki
Jump to navigation Jump to search

Overview

Fusion Registry Core supports Oracle for both its main operating database and as an Externally Managed Data Source.
Warning.PNG Oracle is not supported as a Registry Managed Data Source.

Compatibility

Oracle Release
Oracle database 11g
Oracle database 12c
Oracle database 18c
Oracle database 19c

JDBC Drivers

Fusion software does not ship with the Oracle JDBC drivers for licensing reasons. The correct driver for your Oracle database release and Java version can be downloaded from Oracle.

In general, use ojdbc8.jar for Java 8,9 or 11, and ojdbc10.jar for Java 10.

Installing the JDBC Driver

The driver jar needs to be copied to a suitable location on the machine running Fusion Registry. For instance:

c:\oracle\ojdbc8.jar

And needs to be explicitly added to the CLASSPATH. In Tomcat, this can be done in the Tomcat setenv file.

Configuring the Oracle Database Connection

Operating Database

Configure the operating database connection either on first install of the Fusion Registry Core webapp, or in service when to need to change to a different database.

Simple Configuration

OracleOperatingSimple.PNG
Database Type: Oracle
Server:                      The hostname or IP address of the Oracle database server
Port:                        The Oracle service port number - usually 1521
Schema:                      The Oracle 'service name'
UserId:                      The id of a pre-existing Oracle user with sufficient privileges to create and delete tables, and insert, update and delete data
Password:                    The password for the Oracle UserId

Advanced Configuration

The advanced configuration option provides additional control over the connection by allowing an explicit Connection String to be defined.

OracleOperatingAdvanced.PNG
Database Type:               Custom
Database Connection String:  jdbc:oracle:thin:<hostname>:<port>/<service name>
                             Other options can be added to the Connection String is required - refer to the Oracle JDBC documentation for details
Dialect:                     org.hibernate.dialect.Oracle10gDialect or org.hibernate.dialect.Oracle12cDialect
Driver Class Name:           oracle.jdbc.driver.OracleDriver
UserId:                      The id of a pre-existing Oracle user with sufficient privileges to create and delete tables, and insert, update and delete data
Password:                    The password for the Oracle UserId

Configuring an Externally Managed Oracle Data Source

Data Sources are configured in the Data Sources Manager - choose 'Data Manager' from the left-hand menu bar.

Simple Configuration

OracleDataSourceSimple.PNG
Connection Id:               A unique ID for the connection [A-Za-z0-9_]+
Connection Type:             Externally Managed
Database Cache:              None - No caching
                             Fusion Store - The data is replicated to the built-in in-memory database for additional query performance, but may require additional memory          
Database Platform:           Oracle
Connection Settings:         Simple
Server:                      The hostname or IP address of the Oracle database server
Port:                        The Oracle service port number - usually 1521
Schema:                      The Oracle 'service name'
Username:                    The id of a pre-existing Oracle user with sufficient privileges to create and delete tables, and insert, update and delete data
Password:                    The password for the Oracle UserId

Advanced Configuration

OracleDataSourceAdvanced.PNG
Connection Id:               A unique ID for the connection [A-Za-z0-9_]+
Connection Type:             Externally Managed
Database Cache:              None - No caching
                             Fusion Store - The data is replicated to the built-in in-memory database for additional query performance, but may require additional memory          
Database Platform:           Oracle
Connection Settings:         Advanced
DB Connection String:        jdbc:oracle:thin:<hostname>:<port>/<service name>
                             Other options can be added to the Connection String is required - refer to the Oracle JDBC documentation for details
Dialect:                     org.hibernate.dialect.Oracle10gDialect or org.hibernate.dialect.Oracle12cDialect
Driver Class Name:           oracle.jdbc.driver.OracleDriver
Database Username:           The id of a pre-existing Oracle user with sufficient privileges to create and delete tables, and insert, update and delete data
Database Password:           The password for the Oracle UserId