How to setup continuous integration for your Oracle Database

This page explains how to setup continuous integration (CI) for your Oracle database with the Deployment Suite for Oracle and TeamCity or Jenkins (see highlighted notes) but the process and command line hooks explained below should help you get set up with any CI system.

If you're not ready to setup continuous integration you can learn more about the benefits it brings to database development.

Prerequisites

1. Install Schema Compare for Oracle command line interface

Schema Compare for Oracle includes a command line interface that must be installed on the build machine. This will enable the TeamCity or Jenkins build agent to call the Schema Compare commands.

2. Install Source Control for Oracle

If you're using SVN or TFS for your version control system, Source Control for Oracle removes the manual processes for source-controlling database changes.

Both tools are included in the Deployment Suite for Oracle:

Video: setting up CI for Oracle with Jenkins

This video guides you through setting up continuous integration for your Oracle database with Jenkins.

Walk through: setting up CI for Oracle with TeamCity or Jenkins

In the following example we're using TeamCity with notes on Jenkins. However, the process and command line hooks explained below should help you get set up with any CI system.

1. Create a new project

In Jenkins this is called a job.

Screenshot: Step 1. Create a new project in TeamCity (job in Jenkins).

2. Create a build configuration

Add artefact paths to produce reports and a deployment script.

In Jenkins this is a post build step to archive files.

PreDeploy.html => Reports
PreDeploy_images => Reports\PreDeploy_images
UpgradeScript.sql => Scripts
PostDeploy.html => Reports
PostDeploy_images => Reports\PostDeploy_images
Screenshot: Step 2. Create a build configuration.

3. Create and attach a version control Settings root

Specify a custom checkout path to use in build steps later

In Jenkins this a custom workspace, under advanced options.

Screenshot: Step 3. Create and attach a version control Settings root.

4. Add build steps

In Jenkins these are called Windows batch commands.

PreDeployReport (compares the contents of svn with a live database and generates a report of the differences). Command Line Executable:

C:\Program Files\Red Gate\Schema Compare for Oracle 3\SCO.exe

Parameters:

/source C:\DatabaseCI{WIDGET} /target User/Pass@host{WIDGET} /includeidentical /report:"PreDeploy.html" /reporttype:Interactive

CreateScriptAndDeploy (create the SQL script and upgrade the target database). Command Line Executable:

C:\Program Files\Red Gate\Schema Compare for Oracle 3\SCO.exe

Parameters:

/source C:\DatabaseCI {WIDGET} /target User/Pass@host{WIDGET} scriptfile:UpgradeScript.sql /deploy

Execute: Only if all previous steps were successful

PostDeployReport (compares the contents of svn with a live db and generates a report of the differences). Command Line Executable:

C:\Program Files\Red Gate\Schema Compare for Oracle 3\SCO.exe

Parameters:

/source C:\DatabaseCI{WIDGET} /target User/Pass@host{WIDGET} /includeidentical /report:"PostDeploy.html" /reporttype:Interactive

Execute: Only if all previous steps were successful

5. Set build failure conditions

Turn off 'fail if build process exit code is not zero'.

Screenshot: Turn off 'fail if build process exit code is not zero'

6. Add a new trigger to build on each check-in

Creation scripts checked in to version control need to be generated by either Source Control for Oracle or Schema Compare for Oracle.

7. A build will create a deployment script and reports as artifacts

A build will generate reports and deployment scripts as artifacts.