Using Oracle as the Relational Database

Using Oracle as the Relational Database

OpenEMPI uses by default the Postgres database as the relational database for storing configuration and other information about the instance. The professional edition of OpenEMPI provides support for a number of other relational databases. In this section we describe the configuration steps for using another supported relational database with OpenEMPI in place of Postgres. In general there are three steps involved in switching from Postgres to another relational database: 1. create the database objects needed to support OpenEMPI on your database, 2. change the configuration file jdbc.properties to point to the database server and 3. add the appropriate JDBC driver to your OpenEMPI instance.

Oracle 12c

• Create the database objects to support OpenEMPI on Oracle 12.2 or later: The OpenEMPI distribution provides scripts for creating the required database objects on the Oracle database server. The scripts are located in the conf directory of the distribution. You must execute each of the scripts in the sequence shown below using an Oracle user account with the appropriate privileges.
  • create_new_database_schema-3.x.0-oracle.sql: creates the primary database objects (tables, sequences, indexes, etc.) that OpenEMPI requires for operation
  • create_person_entity_model_definition-oracle.sql: creates the definition of the default person entity
  • create_person_reference_tables-oracle.sql: creates tables with reference data for backwards compatibility with older versions of OpenEMPI
  • report_definitions-entity.sql: loads configuration data that is used by the reporting module (Professional Edition) to generate reports on demand from the instance
• Copy the JDBC driver to the lib directory in the root directory of your OpenEMPI instance (you should see the postgres driver in that directory as well). You should be able to locate the JDBC driver in either your Oracle database installation directory or in by downloading from the Oracle site. The latest version of the driver we have tested with is: ojdbc8-12.2.0.1.jar
• Modify the configuration of the jdbc.properties file in OPENEMPI_HOME to point to your Oracle database server instance. You must either comment out or remove the configuration properties for the Postgres database with those for your Oracle server as shown below.

jdbc.driverClassName=oracle.jdbc.driver.OracleDriver
jdbc.url=jdbc\:oracle\:thin\:@ORACLE_SERVER-OR_IP_ADDRESS\:PORT\:DATABASE_SID
jdbc.username=openempi
jdbc.password=openempi
hibernate.dialect=org.hibernate.dialect.Oracle10gDialect

example:
jdbc.driverClassName=oracle.jdbc.driver.OracleDriver
jdbc.url=jdbc\:oracle\:thin\:@127.0.0.1\:1521\:openempi_sid
jdbc.username=openempi
jdbc.password=openempi
hibernate.dialect=org.hibernate.dialect.Oracle10gDialect