Comparing the FlexDeploy JDBC and Oracle Database Plugins

The use of relational databases to store and retrieve data is common in most applications. As applications evolve over time, changes to the underlying database structures are often required. The management and implementation of the changes across multiple test and production environments can become a time-consuming and error-prone process without the use of a DevOps and CI/CD tool. In this blog article, we will compare how FlexDeploy supports two different methods of implementing relational database changes using the JDBC plugin and the Oracle Database plugin.

Two Database Plugins

FlexDeploy offers two options to handle database object changes that can be utilized to automate the change migration process.

The JDBC Plugin

The first is the JDBC plugin, which offers the ability to execute almost any DML (data manipulation language) or DDL (data definition language) statements against almost any JDBC compliant database. The primary function of the JDBC plugin is to take SQL statements from one or more files and execute them against the database in a user-supplied sequence.

The Oracle Database Plugin

The second is the Oracle Database plugin which offers the ability to determine what has changed between a new version of an object and the exiting object in the Oracle database. The primary function of the Oracle Database plugin is to take a new version of an Oracle object defined in either a DDL file or source database and compare it to the target database to identify and apply any differences.

Comparing the Plugins’ Major Feature/Functions

Database Change Example

Here is an example that shows how each plugin would handle a simple database change to add a new column to an existing table.

Current table definition:

CREATE TABLE HR.REGIONS 
   (	REGION_ID NUMBER NOT NULL ENABLE, 
      REGION_NAME VARCHAR2(25 BYTE), 
      CONSTRAINT REG_ID_PK PRIMARY KEY (REGION_ID)
        USING INDEX (CREATE UNIQUE INDEX HR.REG_ID_PKX ON HR.REGIONS (REGION_ID) ));

Change needed: Add a column named REGION_CODE with a data type of VARCHAR2(2 BYTE)

For the purposes of this example, we are using an Oracle database, and the DDL to create the table is checked into a source control system (e.g. Git, Subversion, etc.).

Using the JDBC plugin, the process would be as follows:

• Create an SQL file that contains: ALTER TABLE HR.REGIONS ADD REGION_CODE VARCHAR2(2 BYTE).
• Check the file into a source control system.
• Run the FlexDeploy build/deploy to execute the alter statement using the JDBC plugin.
• Use FlexDeploy to deploy to remaining environments and execute the same alter statement.

Using the Oracle Database plugin, the process would be as follows:

• Retrieve the current file containing the create table from the source control system.
• Update the create table statement to add the additional column.
• Check the file back into the source control system.
• Run the FlexDeploy build/deploy for the Oracle Database plugin to compare the new DDL to the existing table in the environment. The plugin will generate the necessary steps to add the column and any other changes that are needed to synchronize the actual table with the new DDL.
• Use FlexDeploy to deploy to remaining environments. The plugin will compare the table in that environment to the new structure and generate the necessary DDL statements to synchronize the table to the new DDL.

The net result is that both plugins are able to add the column to the existing table. The main difference is what the developer needs to change/create in the input DDL to have the change initiated. The JDBC plugin requires the developer to make the determination of what needs to change and code that in the input file. The Oracle Database plugin is able to determine what changes need to be made regardless of the state of the target environment.

Additional Features

An additional feature of the JDBC plugin is that it supports executing DML statements in addition to making object changes. Additionally, the JDBC plugin works against almost any JDBC-compliant relational database while the Oracle Database plugin only supports the Oracle database.

Here are some additional blogs about the JDBC plugin:

Make Database Changes with the JDBC Plugin
Automate the Deployment of Database Objects

An additional feature of the Oracle Database plugin is that it can synchronize out-of-sync environments. For instance, if one environment gets restored to a point in time or refreshed from production, the Oracle Database plugin will compare the existing structure in that environment to the new versions being deployed to determine what changes need to be implemented. The JDBC plugin would require manually determining which DDL files would need to be re-executed.

Both the JDBC and the Oracle Database plugins provide the capabilities to make database object changes. The primary difference is how those changes are initiated by the developers. The FlexDeploy plugins allow both mechanisms to be used to deploy database changes in the same framework.