Database DevOps for Oracle

Analogous to checking that code compiles, a database build will check that the database can be created from scratch. Failures should be rare as changes have in theory originated from a working development instance linked to version control using Source Control for Oracle. However, SQL errors could be introduced in specific circumstances.

A user chooses to make a change "offline" by editing the SQL creation scripts directly, rather than making a "connected" change on their development instance and using Source Control for Oracle to do the check-in.
A poorly executed code merge could result in invalid SQL.

Note that Oracle will not prevent a database from building if it contains invalid objects. These need to be detected independently.

Other benefits

Other than failing fast when either of the above scenarios occurs, running a database build has additional benefits:

It builds a database on which further testing can happen, such as unit testing and static analysis checks.
A report of all objects in the database can be generated as a build artifact for documentation or audit purposes.
A database creation script can be generated as a build artifact, which is can be run to easily provision dev and test instances.

Worked example:

Runs DropAllObjects.sql (code included below) to empty the TEST schema, which is a schema whose sole function is to serve the Build and Test continuous integration process.
Runs the Schema Compare for Oracle command line, sco.exe, to build the database from the State folder, where the creation scripts are stored.
As part of the same command line invocation we also output the .sql creation script and the changes report to save as build artifacts.
We output a warning to the console if there are no changes, which probably means the schema has no objects
If, as expected, we detect differences (sco.exe exit code 61) we set the ERRORLEVEL to 0 to prevent the build from recognizing this as a failure.

CI Build

rem First we run a previously saved script Tools/DropAllObjects.sql to drop all objects from the TEST schema

echo on
Call exit | sqlplus SOCO_TEST/demopassword@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=localhost)(Port=1521))(CONNECT_DATA=(SID=XE))) @Tools/DropAllObjects.sql
echo off

rem Build the database with the objects and generate a creation script and a report listing all objects. 
"C:\Program Files\Red Gate\Schema Compare for Oracle 4\sco.exe" /deploy /i:sdwgvac /source State{SOCO_DEV} /target SOCO_TEST/demopassword@localhost/XE{SOCO_TEST} /sf:Artifacts/database_creation_script.sql /report:Artifacts/all_objects_report.html

echo Build database from state:%ERRORLEVEL%

rem IF ERRORLEVEL is 0 then there are no changes.
IF %ERRORLEVEL% EQU 0 (
    echo ========================================================================================================
    echo == Warning - No schema changes detected. Does the database have any schema objects?
    echo ========================================================================================================
)

rem IF ERRORLEVEL is 61 there are differences, which we expect.
IF %ERRORLEVEL% EQU 61 (
    echo ========================================================================================================
    echo == Objects were found and built. Change report all_objects_report.html saved as an artifact
    echo ========================================================================================================
    rem Reset the ERRORLEVEL to 0 so the build doesn't fail 
    SET ERRORLEVEL=0
)

DropAllObjects.sql

SET SERVEROUTPUT ON SIZE 1000000
BEGIN
  FOR cur_rec IN (SELECT object_name, object_type 
                  FROM   user_objects
                  WHERE  object_type IN ('TABLE', 'VIEW', 'PACKAGE', 'PROCEDURE', 'FUNCTION', 'SEQUENCE')) LOOP
    BEGIN
      IF cur_rec.object_type = 'TABLE' THEN
        EXECUTE IMMEDIATE 'DROP ' || cur_rec.object_type || ' "' || cur_rec.object_name || '" CASCADE CONSTRAINTS';
      ELSE
        EXECUTE IMMEDIATE 'DROP ' || cur_rec.object_type || ' "' || cur_rec.object_name || '"';
      END IF;
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.put_line('FAILED: DROP ' || cur_rec.object_type || ' "' || cur_rec.object_name || '"');
    END;
  END LOOP;
END;
/

Database DevOps for Oracle

Database build

Other benefits

Worked example: